Is there a formula to transpose numbers, e.g. change 36 to 63

[greg.jobs]

Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

 

[David Biddulph]

If it is always a 2 digit integer, you could use =LEFT(A1)+10*RIGHT(A1)

 

[Steve Yandl]

You could create a user defined function like the following:

________________________________________

Function Switcheroo(x As Integer) As Integer
Dim strDigits As String
Dim strRev As String
Dim n As Integer

strDigits = CStr(x)
n = Len(strDigits)

Do Until n = 0
strRev = strRev & Right(strDigits, 1)
strDigits = Left(strDigits, n - 1)
n = n - 1
Loop

Switcheroo = CInt(strRev)

End Function

__________________________________________

Steve

 

[Earl Kiosterud]

Greg,

Please don't post the same question in multiple newsgroups, unless you've indicated in your
post that you've done that, and to which newsgroup you wish to have replies. It causes
redundant effort on the part of the responders when they don't see that your question was
already answered in another group, and then they say bad words.

Here's a user-defined function that will handle any number of digits. It returns a text
string, so you'll get zeroes properly.

Function Reverse(indata As Range) As String
Reverse = StrReverse(indata.Text)
End Function

Paste it from here into a regular module in your workbook, then call it in a cell, to wit:

=Reverse(A2)

 

[Randy Harmelink]

If the number is in cell E5, this array-entered formula should do the
trick:

=SUM(10^(ROW(A1:A50)-1)*MID(E5&REPT("0",50),ROW(A1:A50),1))

 

[greg.jobs]

Greg,

Please don't post the same question in multiple newsgroups, unless you've indicated in your
post that you've done that, and to which newsgroup you wish to have replies. It causes
redundant effort on the part of the responders when they don't see that your question was
already answered in another group, and then they say bad words.

Here's a user-defined function that will handle any number of digits. It returns a text
string, so you'll get zeroes properly.

Function Reverse(indata As Range) As String
Reverse = StrReverse(indata.Text)
End Function

Paste it from here into a regular module in your workbook, then call it in a cell, to wit:

=Reverse(A2)

Erid,

Sorry, I'm totally new to this.

I wanted to post to many groups so I could expose my question to lots
of clever people. Should I just stick with one group?

Thanks
Greg (A Brit)

 

[greg.jobs]

If it is always a 2 digit integer, you could use =LEFT(A1)+10*RIGHT(A1)

That is the perfect answer

What if it was

- always 4 digits e.g. 4000
- always 5 digits e.g. 10,000

(and I wanted to transpose the 2nd and 3rd digits)



If I wanted to do this to MANY MANY cells, would it be more suitable
to use a formula or to write a macro?

Kind regards
Greg

 

[Earl Kiosterud]

Greg,

Actually, the way you posted it, all the groups into which you'd posted it showed up in the
"Newsgroups" box in Outlook Express when replying. Replies using Outlook Express would
appear in all the groups, and all would see the reply and there'd be no problem. But some
folks don't connect directly to Microsoft's newsgroup servers, instead using various web
sites. I don't know how those web sites work when something is posted to multiple groups.
Many posters post separately to multiple groups, so the above doesn't happen -- that
definitely causes the problem.

Many folks in these Excel groups read many of the groups, and so posting to the one most
relevant group is considered best. Some folks, if multi-posting, put something like
"respond to excel.misc." But folks wouldn't, in that case, necessarily know if the question
has been answered in another group, unless an earlier reply got posted to all the groups, as
I said.

It looks as though Randy Harmelink's reply might be your best bet for the transposition you
want. It handles numbers up to 150 digits, and doesn't require messing with a UDF. Be sure
to use Ctrl-Shift-Enter any time you've edited the formula, as it's an array formula.

 

[Pete_UK]

Greg,

most of the regular respondents reply to several of the groups (I
usually look in six), so multi-posting is not necessary, as Earl says.
Also, most replies are top-posted, so it messes up the reponses if you
bottom-post.

Pete (also a Brit)

 

[JE McGimpsey]

For those of us using newsreaders, your cross-post should be fine (don't
know about OE- it's kind of a poor cousin to newsreaders). I don't ever
use web-based portals, but for them I suspect it would depend on how
they implemented their portal.

It's generally unnecessary to cross-post, since most of the 'regulars'
read the three you crossed to, but it's *FAR* better than posting
separately to each group.

 

[Ron Rosenfeld]

What if it was

- always 4 digits e.g. 4000
- always 5 digits e.g. 10,000

(and I wanted to transpose the 2nd and 3rd digits)



If I wanted to do this to MANY MANY cells, would it be more suitable
to use a formula or to write a macro?

Kind regards
Greg

Well, if you want to have a more general solution, of the type you allude to
above, you could use a UDF that captures each digit separately, and then return
them in the order you want. This solution uses Regular Expressions, and can
just as easily reverse two letters in a string as two digits, but it could be
restricted just to digits if you prefer.

Enter this UDF in a regular module:

===================================
Option Explicit
Function RESwap(str As String, return_pattern) As String
Dim re As Object
Dim mc As Object
Dim sPat As String
Dim ct As Long
Set re = CreateObject("VBScript.regexp")
re.Global = True
re.Pattern = "\w"
If re.test(str) = True Then
Set mc = re.Execute(str)
ct = mc.Count
sPat = "^" & Application.WorksheetFunction.Rept("(\w)", ct)
re.Pattern = sPat
RESwap = re.Replace(str, return_pattern)
End If
End Function
=============================

In some cell, enter the function:

=RESwap(A1, return_pattern)

A1 can be a cell reference or an actual string of digits or letters.

The return pattern is composed of a string numbers each preceded by a "$".
These correspond to each place in the original string.

So two reverse two digits, return_pattern would be "$2$1"

If you have 4 digits and want to reverse the 2nd and 3rd, then return_pattern
would be "$1$3$2$4"

If you have 5 digits and want to reverse the 2nd and 3rd, then return_pattern
would be "$1$3$2$4$5"

and so forth.
--ron