提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

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

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

用户定义函数反转文本字符串

用VBA代码按特定分隔符反转字词顺序

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


基于特定分隔符的反向文本顺序:

您订购的 Kutools for Excel反向文本 功能,您可以快速地从右向左反转文本字符串,同时,还可以基于某些特定的分隔符(如逗号,回车符,空格等)来反转它们。

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


箭头蓝色右泡 用户定义函数反转文本字符串


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

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并粘贴在下面的宏 模块窗口。

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

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

4。 然后向下拖动填充手柄以复制此公式,并且单元格中的文本立刻受到尊敬,请参阅截图:


箭头蓝色右泡 用VBA代码按特定分隔符反转字词顺序

如果你有一个由逗号分隔的单元格单词列表,老师,医生,学生,工人,司机“,并且你想扭转这样的单词顺序”驱动器,工人,学生,医生,老师”。 您也可以使用关注VBA来解决它。

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并粘贴在下面的宏 模块 窗口。

Sub ReverseWord()
'Updateby20131128
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反向文本顺序 可以帮助您快速方便地翻转各种文本字符串。 它可以执行以下操作:

将文本从右向左翻转,如“点击一些词“要”sdrow emos pat“;

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

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

从右向左反转文本:

1。 选择您想要反转的范围。

2。 点击 Kutools > 文本工具 > 反向文本顺序,看截图:

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

立即下载并免费试用Kutools for Excel!


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

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

1. 选择单元格并通过单击应用此实用程序 Kutools > 文本 > 反向文本顺序.

2.反向文本 对话框中,选择要分隔单元格值的分隔符,以便基于该单词进行反转,请参阅截图:

3。 然后点击 Ok or 申请,细胞中的词语立刻被颠倒过来。 查看屏幕截图:

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

要了解更多关于这个功能,请访问 反向文本顺序.

立即下载并免费试用Kutools for Excel!


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

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


相关文章:

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


Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

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

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    PRaveen Soni · 7 months ago
    awesome very helpful thanks for sharing the details
  • To post as a guest, your comment is unpublished.
    Huda · 1 years ago
    Thank you very much. This code helped my friend in a very good way
  • To post as a guest, your comment is unpublished.
    vee · 2 years ago
    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..
  • To post as a guest, your comment is unpublished.
    piphat · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Duncan Sullivan-Shaw · 3 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Mohanned Tayyeb · 3 years ago
    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]mohanned1@windowslive.com[/b][/b][/b]




    with regards,
    • To post as a guest, your comment is unpublished.
      Royal Chan · 1 years ago
      please go through the instructions give by the extend office website after searching with google by reverse string in excel.
  • To post as a guest, your comment is unpublished.
    CHARL · 3 years ago
    Hi I would like to reverse Dates.

    20/11/2015 to 2015/11/20

    Any Help?

    Thanks
    • To post as a guest, your comment is unpublished.
      Gaston · 3 years ago
      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.
  • To post as a guest, your comment is unpublished.
    asb · 3 years ago
    thank you
    its really useful for me :roll:
  • To post as a guest, your comment is unpublished.
    VB Developer · 3 years ago
    I notice a lot of people are re-inventing the wheel by doing left, right commands, VB6 supports string reverse

    SYNTAX:
    strRevese("String")

    thats all you need

    Dim stCellValue, i
    For i = 1 To 5
    Range("A" & X).Select
    stCellValue = Range("A" & X).Value
    stCellValue = StrReverse(stCellValue)
    Range("A" & X).Value = stCellValue
    Next i

    Another nifty trick would be to pre-populate zeros, if values under 10 but need to be in a time format such as "10:01:24" rather than "10:1:24"

    intMins = 1
    stMinutes = right("00" & intMins, 2)

    result = 01
  • To post as a guest, your comment is unpublished.
    Emma · 5 years ago
    Use this..put your input in A1 then the output will be in B1

    Sub zzzText()

    Dim xlen, xvalue, xoutput

    xlen = VBA.Len(Range("A1"))
    xvalue = Range("A1").Value
    xoutput = ""

    For i = 1 To xlen

    GetText = VBA.Right(xvalue, 1)
    xvalue = VBA.Left(xvalue, xlen - i)
    xoutput = xoutput & GetText

    Next i

    Range("B1").Value = xoutput
    Range("C1").Value = xlen
    Range("D1").Value = xvalue

    End Sub
  • To post as a guest, your comment is unpublished.
    Danos · 5 years ago
    Hi all, i've tried this function: Sub ReverseText()
    'Updateby20131128
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    For Each Rng In WorkRng
    xValue = Rng.Value
    xLen = VBA.Len(xValue)
    xOut = ""
    For i = 1 To xLen
    getChar = VBA.Right(xValue, 1)
    xValue = VBA.Left(xValue, xLen - i)
    xOut = xOut & getChar
    Next
    Rng.Value = xOut
    Next
    End Sub

    but when i've ran it with numbers it has deleted all the zeros.

    do you know a function that not delete zeros?

    Thanks!!
    • To post as a guest, your comment is unpublished.
      Farhan · 5 years ago
      Use Vineet's function code as a module and when using the formula, use this trick: =strrev(""&D9&"") where D9 contains the number. Vineet's VBA code for that function is:

      Function strrev(strValue As String)
      strrev = StrReverse(strValue)
      End Function
  • To post as a guest, your comment is unpublished.
    Faseeh · 5 years ago
    Sorry a little editing

    Function InvertText(str As String)
    'By Faseeh Muhammad
    Dim curr As String
    Dim m As Integer
    For m = Len(str) To 1 Step -1
    countRepp = countRepp & Mid(str, m, 1)
    Next m
    Inverttext
    End Function
  • To post as a guest, your comment is unpublished.
    Faseeh · 5 years ago
    Another one...

    Function InvertText(str As String)
    'By Faseeh Muhammad
    Dim m As Integer
    For m = Len(str) To 1 Step -1
    countRepp = countRepp & Mid(str, m, 1)
    Next m
    End Function
  • To post as a guest, your comment is unpublished.
    Vineet · 5 years ago
    This is absurd. So much code to perform just a simple task of reversing the string? :o
    Just 3 line function needs to be added in the module as below:

    Function strrev(strValue As String)
    strrev = StrReverse(strValue)
    End Function

    Now the formula =strrev(A1) can be used in Excel sheet. This works since StrReverse is an inbuilt function of VBA. :D