Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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) |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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 - |
#12
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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? |
#13
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Is there a formula to transpose numbers, e.g. change 36 to 63
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I transpose a column of 80 numbers into... | Excel Discussion (Misc queries) | |||
Transpose words and numbers into array of different proportions | Excel Discussion (Misc queries) | |||
Transpose rows to columns w/varying numbers of lines per record | Excel Worksheet Functions | |||
Transpose and order array numbers | Excel Worksheet Functions | |||
Transpose week numbers to date | Excel Discussion (Misc queries) |