Fix dimensions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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.
 
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
 
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))
 
hi
good question. I was just considering his sample data. oh well. thanks for
the insite.

Regards
FSt1
 
Peo, JW, all of your solutions work very well, thank you very much for your
help.
What a time saver. :)
 
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?
 
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?
 
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?
 
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
 
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!
 
Back
Top