Fix dimensions
On Oct 10, 5:45 pm, Niniel wrote:
Hello,
I have dimension in a cell that look like this 11x22x33. I need to separate
that into 3 cells and lose the "x"s. I am able to do a separation, but as a
result I have 11x | 22x | 33.
How can I get rid of the letters?
My data is in cell A3, and I'm using the following formulas in cells B3, C3
and D3:
=LEFT(A3, SEARCH("x",A3,1))
=MID(A3,SEARCH("x",A3,1)+1,SEARCH("x",A3,SEARCH("x ",A3,1)+1)-SEARCH("x",A3,1))
=RIGHT(A3,LEN(A3)-SEARCH("x",A3,SEARCH("x",A3,1)+1))
Thank you.
Option 1:
=SUBSTITUTE(LEFT(A3, SEARCH("x",A3,1)),"x","")
=SUBSTITUTE(MID(A3,SEARCH("x",A3,1)+1,SEARCH("x",A 3,SEARCH("x",A3,1)+1)-
SEARCH("x",A3,1)),"x","")
=RIGHT(A3,LEN(A3)-SEARCH("x",A3,SEARCH("x",A3,1)+1))
Option 2:
=LEFT(A3, SEARCH("x",A3,1)-1)
=MID(A3,SEARCH("x",A3,1)+1,SEARCH("x",A3,SEARCH("x ",A3,1)+1)-
SEARCH("x",A3,1)-1)
=RIGHT(A3,LEN(A3)-SEARCH("x",A3,SEARCH("x",A3,1)+1))
Option 3:
=LEFT(A3,FIND("x",A3,1)-1)
=MID(A3,FIND("x",A3,FIND("x",A3,1))+1,
(FIND("x",A3,FIND("x",A3,1)+1)-1)-FIND("x",A3,1))
=RIGHT(A3,LEN(A3)-FIND("x",A3,FIND("x",A3,1)+1))
|