switch number order

B

brian

i'm trying to figure out if there is any way to switch a column of 6 digit
numbers so that the first 3 numbers are the last 3 and vise versa. example..
111222=222111, or 198321=321198. any help would be welcomed. thanks for
your time.
 
F

Franz Verga

Nel post *brian* ha scritto:
i'm trying to figure out if there is any way to switch a column of 6
digit numbers so that the first 3 numbers are the last 3 and vise
versa. example.. 111222=222111, or 198321=321198. any help would be
welcomed. thanks for your time.
Click to expand...


Hi Brian.

If you want a number to use in calculation use the formula:

=VALUE(RIGHT(A22,3)&LEFT(A22,3))

instead, if you need just a string, you can use the formula:

=RIGHT(A22,3)&LEFT(A22,3)


--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy
 
V

VBA Noob

Here's a helpful bit of VBA code

Option Explicit

Function Reverse(InString) As Variant
' If a string, returns its argument, reversed
' Otherwise returns #N/A error
Dim StringLength As Integer
Dim i As Integer

If Application.WorksheetFunction.IsText(InString) Then
Reverse = ""
StringLength = Len(InString)
For i = StringLength To 1 Step -1
Reverse = Reverse & Mid(InString, i, 1)
Next i
Else
Reverse = CVErr(xlErrNA)
End If
End Function

Press alt F11. Insert a module and paste code. Next enter
=reverse(A1)...Change (A1) as required

VBA Noob
 
K

Kevin Vaughn

I won't be surprised if someone comes up with a more elegant solution, but
this seems to work:

=VALUE(MID(A1,6,1)&MID(A1,5,1)&MID(A1,4,1)&MID(A1,3,1)&MID(A1,2,1)&MID(A1,1,1))

I assumed you would probably want to keep them as numbers therefore the
value function.
 
R

Roger Govier

Hi Brian

With the number to be reversed in cell A1, try

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
(10^(ROW(INDIRECT("1:"&LEN(A1)))))/10)
 
K

Kevin Vaughn

oops, I misunderstood. I thought you were trying to reverse the order.
Disregard this post.
 
R

Roger Govier

Hi Brian

I may have misunderstood your request. My solution was for changing the
order of numbers in a cell.
If your data is in individual cells, going down column A, and you want
them reveres in column B, then try entering in B1
=INDEX($A$1:$A$100,COUNT(A$1:$A$100)+1-ROW(1:1))
and copy down column B as required
 
R

RWS

If you try this

=RIGHT(a1,3)&LEFT(a1,3), and drag for entire range
it will return your number as a text format only, so you cant work with it

Then select the new data, copy and paste Special into the same place, then
click on the little yellow warning marker and convert to number
 
D

Dana DeLouis

Here's two of many options, assuming they are indeed 6 digit numbers.

=MOD(A1,1000)*1000+INT(A1/1000)
=--(RIGHT(A2,3) & LEFT(A2,3))
 
Top