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.

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.


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

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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=554309

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.
--
Kevin Vaughn


"brian" wrote:

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.

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)

--
Regards

Roger Govier


"brian" wrote in message
...
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.

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


"Kevin Vaughn" wrote:

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.
--
Kevin Vaughn


"brian" wrote:

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.

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

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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)

--
Regards

Roger Govier


"brian" wrote in message
...
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.

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


--
RWS


"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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)

--
Regards

Roger Govier


"brian" wrote in message
...
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.

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

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"brian" wrote in message
...
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.