change order of 5 letter word based on another cell containing 5 numbers
 

Hi

This is difficult to explain so please bear with me

Imagine a 5 letter word in cell A1 (ABCDE)
and a 5 digit number in cell A2 (12345)
then cell A3 =ABCDE

if cell A2 =23451
then I need a formula to give the answer as BCDEA in cell A3

A2 is the order I need the letters to be displayed.

Can it be done by a formula?

Can someone help as I dont even know where to start

Kenny W
XP pro and Office 2003

change order of 5 letter word based on another cell containing 5 numbers
 

On Sat, 19 Jul 2008 17:56:46 +0100, "Forum Freak"
wrote:

Hi

This is difficult to explain so please bear with me

Imagine a 5 letter word in cell A1 (ABCDE)
and a 5 digit number in cell A2 (12345)
then cell A3 =ABCDE

if cell A2 =23451
then I need a formula to give the answer as BCDEA in cell A3

A2 is the order I need the letters to be displayed.

Can it be done by a formula?

Can someone help as I dont even know where to start

Kenny W
XP pro and Office 2003


For up to 9 characters, it's pretty easy to do it with a User Defined Function.

To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use it, enter a formula of the type =Swap(A1,A2) where your string is in A1,
and A2 contains the pattern.

If there is a digit in A2 that does not correspond to a "place" in A1, then an
error will be returned.

But you could have patterns such as 11551123115 and it would display:
AAEEAABCAAE

Note that this method will only work for up to nine characters. Your pattern
will need to be differently defined if you want to handle more than nine.

--ron


====================================
Function Swap(str As String, sSwap)
Dim i As Long, lSwapNum As Long
Dim aStr()
ReDim aStr(1 To Len(str))
For i = 1 To Len(str)
aStr(i) = Mid(str, i, 1)
Next i
For i = 1 To Len(sSwap)
lSwapNum = Mid(sSwap, i, 1)
Swap = Swap & aStr(lSwapNum)
Next i
End Function
=================================
--ron

change order of 5 letter word based on another cell containing 5 numbers
 

On Sat, 19 Jul 2008 17:56:46 +0100, "Forum Freak"
wrote:

Hi

This is difficult to explain so please bear with me

Imagine a 5 letter word in cell A1 (ABCDE)
and a 5 digit number in cell A2 (12345)
then cell A3 =ABCDE

if cell A2 =23451
then I need a formula to give the answer as BCDEA in cell A3

A2 is the order I need the letters to be displayed.

Can it be done by a formula?

Can someone help as I dont even know where to start

Kenny W
XP pro and Office 2003



Try the following formula:

=CONCATENATE(MID(A1,MID(A2,1,1);1),MID(A1,MID(A2,2 ,1),1),MID(A1,MID(A2,3,1),1),MID(A1,MID(A2,4,1),1) ,MID(A1,MID(A2,5,1),1))

Hope this helps / Lars-Åke

change order of 5 letter word based on another cell containing 5 numbers
 

Many thanks Ron that was exactly what I wanted.

Regards
Kenny


"Ron Rosenfeld" wrote in message
...
On Sat, 19 Jul 2008 17:56:46 +0100, "Forum Freak"
wrote:

Hi

This is difficult to explain so please bear with me

Imagine a 5 letter word in cell A1 (ABCDE)
and a 5 digit number in cell A2 (12345)
then cell A3 =ABCDE

if cell A2 =23451
then I need a formula to give the answer as BCDEA in cell A3

A2 is the order I need the letters to be displayed.

Can it be done by a formula?

Can someone help as I dont even know where to start

Kenny W
XP pro and Office 2003


For up to 9 characters, it's pretty easy to do it with a User Defined
Function.

To enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste
the
code below into the window that opens.

To use it, enter a formula of the type =Swap(A1,A2) where your string is
in A1,
and A2 contains the pattern.

If there is a digit in A2 that does not correspond to a "place" in A1,
then an
error will be returned.

But you could have patterns such as 11551123115 and it would display:
AAEEAABCAAE

Note that this method will only work for up to nine characters. Your
pattern
will need to be differently defined if you want to handle more than nine.

--ron


====================================
Function Swap(str As String, sSwap)
Dim i As Long, lSwapNum As Long
Dim aStr()
ReDim aStr(1 To Len(str))
For i = 1 To Len(str)
aStr(i) = Mid(str, i, 1)
Next i
For i = 1 To Len(sSwap)
lSwapNum = Mid(sSwap, i, 1)
Swap = Swap & aStr(lSwapNum)
Next i
End Function
=================================
--ron

change order of 5 letter word based on another cell containing 5 numbers
 

Many thanks for your assistance. This formula works!

Regards
Kenny


"Lars-Åke Aspelin" wrote in message
...
On Sat, 19 Jul 2008 17:56:46 +0100, "Forum Freak"
wrote:

Hi

This is difficult to explain so please bear with me

Imagine a 5 letter word in cell A1 (ABCDE)
and a 5 digit number in cell A2 (12345)
then cell A3 =ABCDE

if cell A2 =23451
then I need a formula to give the answer as BCDEA in cell A3

A2 is the order I need the letters to be displayed.

Can it be done by a formula?

Can someone help as I dont even know where to start

Kenny W
XP pro and Office 2003



Try the following formula:

=CONCATENATE(MID(A1,MID(A2,1,1);1),MID(A1,MID(A2,2 ,1),1),MID(A1,MID(A2,3,1),1),MID(A1,MID(A2,4,1),1) ,MID(A1,MID(A2,5,1),1))

Hope this helps / Lars-Åke

change order of 5 letter word based on another cell containing 5 numbers
 

On Sat, 19 Jul 2008 19:30:09 +0100, "Forum Freak"
wrote:

Many thanks Ron that was exactly what I wanted.

Regards
Kenny

Glad to help. Thanks for the feedback.
--ron