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


"Niniel" wrote in message
...
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.

hi
=LEFT(B3,2)
edit to fit your data

regard
FSt1

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

forgot to add
copy and paste as values.

regards
FSt1

"FSt1" wrote:

hi
=LEFT(B3,2)
edit to fit your data

regard
FSt1

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

If you want to use formulas, in B3 use

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

in C3

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

in D3

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


--


Regards,


Peo Sjoblom


"Peo Sjoblom" wrote in message
...
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


"Niniel" wrote in message
...
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.

What if it can be more than 2 digits?


--


Regards,


Peo Sjoblom




"FSt1" wrote in message
...
hi
=LEFT(B3,2)
edit to fit your data

regard
FSt1

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

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


"Niniel" wrote in message
...
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.

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))

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

Regards
FSt1

"Peo Sjoblom" wrote:

What if it can be more than 2 digits?


--


Regards,


Peo Sjoblom




"FSt1" wrote in message
...
hi
=LEFT(B3,2)
edit to fit your data

regard
FSt1

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

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?

"JW" wrote:

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))

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(A30,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





"Niniel" wrote in message
...
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?

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!