Formula for reversing numbers or text?

[mahmed1]

Hi Guys

If i have a number like this 1234567 and i would like to reverse them so they go in this order 7654321. What is the formula to do this and for text also. i.e A B C D E (TO) E D C B A?

AND Also say i also wanted a formula to reverse but not include the 0 at the end i.e if i had 1234567000 i want it to return 7654321 not the 0.

Thank You

 

[Scott Huish]

If you want it in one cell, use the following UDF:

Function Reverseit(r As String)
Dim s As String
s = StrReverse(r)
If IsNumeric(s) Then
    Reverseit = Val(s)
Else
    Reverseit = s
End If
End Function

Excel Workbook
	A	B
1	1234567000	7654321
2	abcdefg	gfedcba
Sheet1

 

[mahmed1]

If you want it in one cell, use the following UDF:

Function Reverseit(r As String)
Dim s As String
s = StrReverse(r)
If IsNumeric(s) Then
    Reverseit = Val(s)
Else
    Reverseit = s
End If
End Function

Excel Workbook
	A	B
1	1234567000	7654321
2	abcdefg	gfedcba
Sheet1

Thank You

Is there a formula approach?

 

[justanotheruser]

Hi.

I think that you need to create a function on VBA using the code that HOTPEPPER has supplied:

<CODE>Function Reverseit(r As String)
Dim s As String
s = StrReverse(r)
If IsNumeric(s) Then
Reverseit = Val(s)
Else
Reverseit = s
End If
End Function</CODE>

Then you can use the formulas as he (?) stated, and I presume that it should work.

 

[jasonb75]

A UDF is a formula approach, if you mean by only using the standard formula functions then I don't think it's possible, the only alternative I can think of is with the use of MCONCAT, which requires the installation of Morefunc.

 

[T. Valko]

Here's a formula that uses worksheet functions that will reverse a numeric string.

A1 = some number like 1234567000

=--TEXT(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)*10^(ROW(INDIRECT("1:" & LEN(A1)))-1)),REPT("0",LEN(A1)))

Result = 7654321

I don't know of a technique using worksheet functions that'll reverse a text string.