几种用EXCEL实现字符串的"颠倒"的方法


http://www.extendoffice.com/documents/excel/1146-excel-reverse-string-word-order.html

这个不错:


Reverse text string with formula in Excel
Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

In Excel, you can use a formula to reverse text string. For example, there are some text string as shown as below in Excel:

Step 1. Type this formula =IF(LEN(A1)<1,"",MID(A1,LEN(A1),1))&IF(LEN(A1)<2,"",MID(A1,LEN(A1)-1,1))&IF(LEN(A1)<3,"",MID(A1,LEN(A1)-2,1))&IF(LEN(A1)<4,"",MID(A1,LEN(A1)-3,1))&IF(LEN(A1)<5,"",MID(A1,LEN(A1)-4,1))into B1, and then press Enter button. See screenshot:

Step 2. Copy this formula to other cells by dragging the bottom right corner of the Cell B1. See screenshot:

Note:

1. In this case, each of text string has five characters, so here the formula has five sections. If the text string has six characters, you need to add &IF(LEN(A1)<6,"",MID(A1,LEN(A1)-5,1)) to the end of the formula.

2. If each text string has a different number of characters in the column, you cannot drag the bottom right corner to copy the formula, you need to type the different formulas one by one.

arrow blue right bubble Reverse text string with VBA
Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

Supposing you have a range of text strings which you want to reverse, such as “add leading zeros in Excel” to “lecxE ni sorez gnidael dda”. You can reverse the text with following steps:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Modulewindow.

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


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

3. Then press F5, a dialog is displayed on the screen, and you need select a range to work with. See screenshot:

4. And then press OK, and all the text strings have been reversed. See screenshot:


arrow blue right bubble Reverse words separated by interval symbol with VBA

If you have a list of cell words which are separated by commas as this “teacher, doctor, student, worker, driver”, and you want to reverse the words order like this “drive, worker, student, doctor, teacher”. You can also use follow VBA to solve it.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module window.

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



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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. Then press F5, a dialog is displayed on the screen, and you need select a range to work with. See screenshot:

4. And then press Ok, another dialog is popped out for you to specify the interval symbol. See screenshot:

5. Then clcik OK, and you can see the words selected are reverse, see screenshot:


arrow blue right bubble A handy way to reverse text string or words order with Kutools for Excel

The Kutools for Excel’s Reverse Text Order can help you quickly and conveniently to reverse various text strings. It can do following operations:

Reverse the text from right to left, such as “tap some words” to “sdrow emos pat”;

Reverse the text are separated by space, such as “apple orange grape” to “grape orange apple”;

Reverse the text are separated by semicolon, comma, carriage return or other specific characters.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

When you have installed Kutools for Excel, you can do as this:

1. Select the range that you want to reverse.

2. Click Kutools > Text Tools > Reverse Text Order, see screenshot:

3. In the Reverse Text dialog box, select the proper option from Separator which are corresponding with the cell values. And you can preview the results from the Preview Pane. See screenshot:

4. Then click OK or Apply. All the cell contents have been revered by space in this example. See screenshots:

Note:Checking Skip non-text cells to prevent you reversing the numbers in selected range.

To know more about this function, please visit Reverse Text Order.


Related article:

How to flip the first and last name in cells in Excel?


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

 

Comments  
+7 #  Vineet  2014-02-04 06:07
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
Reply | Reply with quote | Quote
-1 #  Faseeh  2014-05-28 11:52
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
Reply | Reply with quote | Quote
-1 #  Faseeh  2014-05-28 11:56
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
Reply | Reply with quote | Quote


  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值