Fix dimensions

N

Niniel

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.
 
P

Peo Sjoblom

make a backup copy of the workbook first, do data text to columns, select
delimited, click next and select x as other delimiter and click finish


--


Regards,


Peo Sjoblom
 
P

Peo Sjoblom

If you want to use formulas, in B3 use

=LEFT(A3,SEARCH("x",A3)-1)

in C3

=LEFT(SUBSTITUTE(A3,B3&"x",""),SEARCH("x",SUBSTITUTE(A3,B3&"x",""))-1)

in D3

=MID(SUBSTITUTE(SUBSTITUTE(A3,B3,""),C3,""),3,255)


--


Regards,


Peo Sjoblom
 
N

Niniel

No, Peo, I want a formula to do this for me. I want to be able to drop the
input data into A3 and then copy the output from another cell.

FSt1, thank you, that works. The only problem is that I may have decimals in
the input, eg. 11.11x22.22x33.33.
 
P

Peo Sjoblom

This will work

in B3

=LEFT(A3,SEARCH("x",A3)-1)

in C3

=MID(A3,SEARCH("x",A3)+1,SEARCH("x",A3,LEN(B3)+2)-SEARCH("x",A3)-1)


in D3

=MID(A3,SEARCH("x",A3,LEN(B3)+2)+1,255)


--


Regards,


Peo Sjoblom
 
J

JW

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.
Click to expand...

Option 1:
=SUBSTITUTE(LEFT(A3, SEARCH("x",A3,1)),"x","")
=SUBSTITUTE(MID(A3,SEARCH("x",A3,1)+1,SEARCH("x",A3,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))
 
F

FSt1

hi
good question. I was just considering his sample data. oh well. thanks for
the insite.

Regards
FSt1
 
N

Niniel

Peo, JW, all of your solutions work very well, thank you very much for your
help.
What a time saver. :)
 
N

Niniel

So the "-1" at the end of the LEFT and MID formulas (option 2) make Excel
drop the last character, ie the "x"? Is that how this works?
 
N

Niniel

One other thing.

I have a whole column with these dimensions, but there are empty rows there,
so it's not a continuous column of data. As a result, I get cells with
"#VALUE!" in them. Can I filter those out somehow?
 
N

Niniel

Figured that one out:

=IF(A3>0,LEFT(A3, SEARCH("x",A3,1)-1),"") etc.

Sometimes, the value in A consist only of letters, however, which, of
course, don't need to be run through the formulas and just give an error
anyway. Can I get a blank cell in cases like that too?
 
P

Peo Sjoblom

In B3

=IF(A3="","",LEFT(A3,SEARCH("x",A3)-1))


in C3


=IF(B3="","",MID(A3,SEARCH("x",A3)+1,SEARCH("x",A3,LEN(B3)+2)-SEARCH("x",A3)-1))


in D3


=IF(C3="","",MID(A3,SEARCH("x",A3,LEN(B3)+2)+1,255))



will return blank cells instead of value errors



--


Regards,


Peo Sjoblom
 
N

Niniel

Neat, thank you.

I also figured out my other issue - I'm running an IsNumber check in another
formula in a neighbouring cell that resorts the output of this operation from
largest number to smallest.

I'm all set now. Thanks for all the help!
 
Top