How to rearrange data using functions and formula!?

[0Cool]

Hi everyone, I have an excel file where in column A there are data in the format HxLxW and in column B I want get the values in LxWxH format using a formula and/or fucntion. How can I do that.
Sample file data:
before.png

Expected file data:
after.png

I've used the text to column option with 'x' as the delimeter to separate the text in three parts and then using D2&"x"&E2&"x"&C2 in column B to get the result but I was hoping for a simple one go solution without macro's or text to column and then add a separate formula to join.

Is there is single formula solution for this particular case?

[AliGW]

In C2 copied down:

=MID(A2,FIND("x",A2)+1,20)&"x"&LEFT(A2,FIND("x",A2)-1)

[jason.b75]

How about

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Shareez Saleem]

LEFT(A2,SEARCH("x",A2,1)-1)

LEFT(MID(SUBSTITUTE(A2,"x","&",2),SEARCH("x",SUBSTITUTE(A2,"x","&",2),1)+1,SEARCH("&",SUBSTITUTE(A2,"x","&",2),1)),SEARCH("&",MID(SUBSTITUTE(A2,"x","&",2),SEARCH("x",SUBSTITUTE(A2,"x","&",2),1)+2,SEARCH("&",SUBSTITUTE(A2,"x","&",2),1)-1)))

RIGHT(SUBSTITUTE(A2,"x","&",2),LEN(SUBSTITUTE(A2,"x","&",2))-SEARCH("&",SUBSTITUTE(A2,"x","&",2)))

[AliGW]

Shareez - the OP asked for a one-go solution, not a way to split the string into three.

Your second and third formulae are unnecessarily long.

[0Cool]

HI AliGW, can you explain how your formula works?

Thanks in advance

[AliGW]

Yes. Let's break it down.

=MID(A2,FIND("x",A2)+1,20)&"x"&LEFT(A2,FIND("x",A2)-1)

=MID(A2,FIND("x",A2)+1,20)&"x"&LEFT(A2,FIND("x",A2)-1)

=FIND("x",A2) returns the position of the first "x" in the string - if you add one, you get the starting position of your second dimension and if you subtract one you get the last character in the string that is the first dimension

=MID(A2,FIND("x",A2)+1,20) ...

This returns the 20 characters that start at the character after the first "x" in the string.

... &"x"& ...

This concatenates the first formula result with the second (the characters up to the first "x").

... LEFT(A2,FIND("x",A2)-1)

This returns the first dimension in the string.

[0Cool]

Whats with the number 20 in the mid function,why did you choose 20 and not any other? can I change that number?

[0Cool]

Hi jason.b75, can you explain your approach? The second/shorter version formula....
Thanks in advance.

[jason.b75]

Sure, I've used the MID function to take the middle of the measurement after duplicating it.

=A2&"x"&A2 gives you 12.3x15.2x13x12.3x15.2x13

FIND("x",A2)+1 finds the first x in A2, the +1 at the end tells the formula to start 1 character after the "x".

LEN(A2) counts the number of characters in A2 and takes the same number of characters from the middle of the part that was joined together earlier (the bit in red).

[AliGW]

Whats with the number 20 in the mid function,why did you choose 20 and not any other? can I change that number?

It can be anything that is long enough. Yes, you can change it: change it to 5 and see what happens (hint: 5 will be too small).

[0Cool]

Thank you both AliGW and jason.b75