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

Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

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

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

wrote in message
ps.com...
Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

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

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


wrote in message
ps.com...
Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

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

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)

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

wrote in message
ps.com...
Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

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

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))

On Sep 20, 1:36 pm, wrote:
Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

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

Hi,

here is 2 ways for just 2 digits numbers:

=A1+(RIGHT(A1,1)-LEFT(A1,1))*9
=VALUE(RIGHT(A1,1)&LEFT(A1,1))

Thanks
--
Farhad Hodjat


" wrote:

Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

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

You can skip all the ,1 entries, as LEFT and RIGHT default to one character
if num_chars is not specified.
--
David Biddulph

"Farhad" wrote in message
...
Hi,

here is 2 ways for just 2 digits numbers:

=A1+(RIGHT(A1,1)-LEFT(A1,1))*9
=VALUE(RIGHT(A1,1)&LEFT(A1,1))

Thanks
--
Farhad Hodjat


" wrote:

Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

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

On Sep 20, 10:09 pm, "Earl Kiosterud" wrote:
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)

--
Regards from Virginia Beach,

Earl Kiosterudwww.smokeylake.com

wrote in message

ps.com...

Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

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)

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

On Sep 20, 9:43 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
If it is always a 2 digit integer, you could use =LEFT(A1)+10*RIGHT(A1)
--
David Biddulph

wrote in message

ps.com...

Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

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

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

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.

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

wrote in message
oups.com...
On Sep 20, 10:09 pm, "Earl Kiosterud" wrote:
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)

--
Regards from Virginia Beach,

Earl Kiosterudwww.smokeylake.com

wrote in message

ps.com...

Using Excel is it possible to 'transpose' numbers

e.g. 48 becomes 84?

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)

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

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)

On Sep 20, 10:51 pm, wrote:

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)- Hide quoted text -

- Show quoted text -

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

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.




In article .com,
wrote:

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?

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

On Thu, 20 Sep 2007 15:03:33 -0700, wrote:

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