跳到主要内容

如何在Excel中反转文本字符串或单词顺序?

作者:晓阳 最后修改时间:2020-06-04

使用Excel工作表时,如何在Excel中反转文本字符串或单词顺序? 例如,您想将“Excel是对我们有用的工具“要”su lof lufesu a si lexxE”。 有时您可能会颠倒顺序,例如“Excel,Word,PowerPoint,OneNote“要”OneNote,PowerPoint,Word,Excel”。 通常这很难解决这个问题。 请查看以下方法:

使用用户定义的功能反转文本字符串

反向单词顺序由特定分隔符使用VBA代码分隔

使用 Kutools for Excel 快速轻松地反转文本字符串或单词顺序


箭头蓝色右气泡 使用用户定义的功能反转文本字符串

假设您有一系列要反转的文本字符串,例如“在Excel中添加前导零“要”lecxE ni sorez gnidael dda”。 您可以按照以下步骤撤消文本:

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic应用程序 窗口。

2。 点击 插页 > 模块,然后将以下宏粘贴到 模块窗口。

Function Reversestr(str As String) As String
    Reversestr = StrReverse(Trim(str))
End Function

3。 然后保存并关闭此代码,返回工作表,并输入以下公式: = reversestr(A2) 放入空白单元格以放入结果,请参见屏幕截图:

4。 然后向下拖动填充手柄以复制此公式,单元格中的文本立即得到确认,请参见屏幕截图:


箭头蓝色右气泡 反向单词顺序由特定分隔符使用VBA代码分隔

如果您有一个用逗号分隔的单元格列表,例如“老师,医生,学生,工人,司机”,而您想要颠倒诸如“驾驶,工人,学生,医生,老师”。 您也可以使用Follow VBA来解决它。

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic应用程序 窗口。

2。 点击 插页 > 模块,然后将以下宏粘贴到 模块 窗口。

Sub ReverseWord()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Sigh As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Sigh = Application.InputBox("Symbol interval", xTitleId, ",", Type:=2)
For Each Rng In WorkRng
    strList = VBA.Split(Rng.Value, Sigh)
    xOut = ""
    For i = UBound(strList) To 0 Step -1
        xOut = xOut & strList(i) & Sigh
    Next
    Rng.Value = xOut
Next
End Sub

3。 然后按 F5 键,将显示一个对话框,请选择要使用的范围。 看截图:

4。 然后按 Ok,会弹出另一个对话框,供您指定要基于其反转单词的分隔符,请参见屏幕截图:

5. 然后点击 OK,您会看到所选单词被颠倒了,请参见屏幕截图:


箭头蓝色右气泡 使用 Kutools for Excel 快速轻松地反转文本字符串或单词顺序

我们推荐使用 Kutools for Excel反向文字顺序 可以帮助您快速方便地反转各种文本字符串。 它可以执行以下操作:

从右向左反转文本,例如“点击一些单词“要”斯德罗·埃莫斯·帕特“;

反向文本由空格或其他特定字符分隔,例如“苹果橙葡萄“要”葡萄橙苹果“;

Kutools for Excel : 带有300多个便捷的Excel加载项,可以在30天内免费试用

从右到左反转文本:

1。 选择要撤消的范围。

2。 点击 库工具 > 文字工具 > 反向文字顺序,请参见屏幕截图:

3。 在 反向文字 对话框,从中选择适当的选项 分隔器 与单元格值相对应。 您可以预览 预览窗格。 看截图:

 立即下载和免费试用Excel的Kutools!


反向文本由空格或其他特定字符分隔:

此功能还可以帮助您反转由特定字符分隔的文本字符串。

1. 选择单元格,然后单击以应用此实用程序 库工具 > 文本 > 反向文字顺序.

2.反向文字 对话框中,选择分隔符,该分隔符用于分隔要根据其反转单词的单元格值,请参见屏幕截图:

3。 然后点击 Ok or 申请,单元格中的单词立即被反转了。 查看屏幕截图:

备注:检查 跳过非文字 单元格,以防止您反转所选范围内的数字。

要了解有关此功能的更多信息,请访问 反向文字顺序.

立即下载和免费试用Excel的Kutools!


箭头蓝色右气泡 演示:使用 Kutools for Excel 根据特定分隔符反转文本字符串

Kutools for Excel:具有300多个方便的Excel加载项,可以在30天内免费试用,没有任何限制。 立即下载并免费试用!

相关文章:

如何在Excel中的单元格中翻转名字和姓氏?

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!

 

Comments (20)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
=UNIRTEXTO("";1;EXT.TEXTO(A2;{20\19\18\17\16\15\14\13\12\11\10\9\8\7\6\5\4\3\2\1};1))
This comment was minimized by the moderator on the site
Ik zoek een manier om een heleboel woorden in 'rijm-fabetische volgorde' te zetten. Dus de laatste letters van het woord moeten in alfabetische volgorde. Iemand enig idee? VrGr Amanda
This comment was minimized by the moderator on the site
This is truly a great help ... Thank you so much
This comment was minimized by the moderator on the site
awesome very helpful thanks for sharing the details
This comment was minimized by the moderator on the site
Thank you very much. This code helped my friend in a very good way
This comment was minimized by the moderator on the site
Is it possible to start the reverse at a certain character length (for the user-defined function or VBA code)? For example, reverse cell A1 values starting after the character count equal to B1 value. So if B1 is 10 characters reverse will start at characters 11+ in A1..
This comment was minimized by the moderator on the site
I found that the original Sub gave out the results with additional sigh at the end of all selected cells. So I fixed this part. For i = UBound(strList) To 0 Step -1 If i > 0 Then xOut = xOut & strList(i) & Sigh Else xOut = xOut & strList(i) Next Rng.Value = xOut
This comment was minimized by the moderator on the site
It's possible to reverse text using formula, it is repetitive and limited to how far you are prepared to go and subject to number of characters excel will allow in the formula bar. Max length of text will be 30 chars and is in cell A1. Cell B1 would read: =TRIM(MID(LEFT(A1&REPT(" ",31),31),30,1)&MID(LEFT(A1&REPT(" ",31),31),29,1)&MID(LEFT(A1&REPT(" ",31),31),28,1)&MID(LEFT(A1&REPT(" ",31),31),27,1)&MID(LEFT(A1&REPT(" ",31),31),26,1)&MID(LEFT(A1&REPT(" ",31),31),25,1)&MID(LEFT(A1&REPT(" ",31),31),24,1)&MID(LEFT(A1&REPT(" ",31),31),23,1)&MID(LEFT(A1&REPT(" ",31),31),22,1)&MID(LEFT(A1&REPT(" ",31),31),21,1)&MID(LEFT(A1&REPT(" ",31),31),20,1)&MID(LEFT(A1&REPT(" ",31),31),19,1)&MID(LEFT(A1&REPT(" ",31),31),18,1)&MID(LEFT(A1&REPT(" ",31),31),17,1)&MID(LEFT(A1&REPT(" ",31),31),16,1)&MID(LEFT(A1&REPT(" ",31),31),15,1)&MID(LEFT(A1&REPT(" ",31),31),14,1)&MID(LEFT(A1&REPT(" ",31),31),13,1)&MID(LEFT(A1&REPT(" ",31),31),12,1)&MID(LEFT(A1&REPT(" ",31),31),11,1)&MID(LEFT(A1&REPT(" ",31),31),10,1)&MID(LEFT(A1&REPT(" ",31),31),9,1)&MID(LEFT(A1&REPT(" ",31),31),8,1)&MID(LEFT(A1&REPT(" ",31),31),7,1)&MID(LEFT(A1&REPT(" ",31),31),6,1)&MID(LEFT(A1&REPT(" ",31),31),5,1)&MID(LEFT(A1&REPT(" ",31),31),4,1)&MID(LEFT(A1&REPT(" ",31),31),3,1)&MID(LEFT(A1&REPT(" ",31),31),2,1)&MID(LEFT(A1&REPT(" ",31),31),1,1)) Result: A1: Duncan Sullivan-Shaw B1: wahS-navilluS nacnuD Basically your text in cell A1 becomes 31 characters long by adding enough spaces to enable this, and each character is read singularly from right to left until you reach the first character. This will result in your reversed text containing leading spaces, which the TRIM command removes for you. To reduce or increase the length you would remove or add each statement. The number within the REPT statement should be 1 more than the maximum length you are working to and your first MID statement will start at the maximum length working down to 1.
This comment was minimized by the moderator on the site
hi all, i don't have Kutools to reverse a character of text, if anybody has this tools and want to help me, please send me email to send my file to you for reverse. my email is: [b][b][/b][/b] with regards,
This comment was minimized by the moderator on the site
please go through the instructions give by the extend office website after searching with google by reverse string in excel.
This comment was minimized by the moderator on the site
Hi I would like to reverse Dates. 20/11/2015 to 2015/11/20 Any Help? Thanks
This comment was minimized by the moderator on the site
For dates all you need to do is change the format of the cell to the format needed. Right click on the cell you would like to reverse and select format cell. Select Date under the category section and change the location from wherever you are to US or Czech or another country that may use the format you are interested in. Then pick the one that matches your requirements.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations