How do you separate "31.6x50" from a cell with text?

[HANNC01]

Hi,

I'm making labels for about 2,000 tiles in the showroom I work at and need to have the size of the tile in a separate column from the name for mail merging.

Example:
[TABLE="width: 271"]
<tbody>[TR]
[TD="class: xl69, width: 271"]Column A
ABBA Azul 31.6x5

I need it to be:
Column A Column B
ABBA Azul 31.6x5

I've looked at forums explaining how to take out numbers from addresses but because mine has numbers, full stops and x's I thought it would be worth asking if anyone could help me.

Any help is appreciated - thank you in advance![/TD]
[/TR]
</tbody>[/TABLE]

 

[HANNC01]

You have the tile size already in C column I guess, let's use that.
Depending on your locale you may need to do it slightly differently.
If your Excel uses commas as decimal separators try this:
=0,01 * 0,01 * VALUE(SUBSTITUTE(RIGHT(C1, LEN(C1) - FIND("x", C1)), ".", ",")) * VALUE(SUBSTITUTE(LEFT(C1, FIND("x", C1) - 1), ".", ","))
Otherwise it's slightly simpler:
=0.01 * 0.01 * VALUE(RIGHT(C1, LEN(C1) - FIND("x", C1))) * VALUE(LEFT(C1, FIND("x", C1) - 1))

This formula gives you tile area which you can use to compute more stuff.

Hi -
Thanks for the reply - it hasn't worked for me, I changed the cell from C1 to E2 but it came out as #VALUE!
Does it matter if the 'x' is upper or lower case?

Thanks.

 

[stan423321]

Yes, my formula only splits on lower case 'x' I'm afraid. As a stopgap solution, try replacing both occurrences of FIND("x", E2) with FIND("X", SUBSTITUTE(E2, "x", "X")).

 

[Rick Rothstein]

Yes, my formula only splits on lower case 'x' I'm afraid. As a stopgap solution, try replacing both occurrences of FIND("x", E2) with FIND("X", SUBSTITUTE(E2, "x", "X")).

No need to do it that way... you can just change FIND to SEARCH and that will take care of the letter casing problem.