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]

 

[XOR LX]

Hi.

For the number portion, if we find the first number in the string, and extract as far as the last number in the string, will that always be guaranteed to give you what you want?

Regards

 

[My Aswer Is This]

Assuming the size is always six characters long
Try this for the size: =RIGHT(A1,6)
Try this for the type: =LEFT(A1,LEN(A1)-6)
This sample assumes your value is in range “A1”
And then you could use fill down

 

[HANNC01]

Hi XOR LX & My Answer Is This - Thank you very much for your help.
Unfortunately, the sizes and names are different sizes:
[TABLE="width: 271"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ABBA Azul 31.6x5[/TD]
[/TR]
[TR]
[TD]ABBA Naranja 31.6x5[/TD]
[/TR]
[TR]
[TD]ABBA Pistachio 31.6x5[/TD]
[/TR]
[TR]
[TD]ABBA Rojo 31.6x5[/TD]
[/TR]
[TR]
[TD]AEGYPTUS Mix 10.5x10.5[/TD]
[/TR]
[TR]
[TD]AEGYPTUS Tebe Noce 16.3x16.3[/TD]
[/TR]
[TR]
[TD]AEGYPTUS Tebe Noce 32.7x32.7[/TD]
[/TR]
[TR]
[TD]AEGYPTUS Tebe Noce 49x16.3[/TD]
[/TR]
[TR]
[TD]AEGYPTUS Tebe Noce 49x32.7

The formula =LEFT(A1,LEN(A1)-6) works well for the name of the tiles so is there one for the sizes instead?

Thanks again.[/TD]
[/TR]
</tbody>[/TABLE]

 

[stan423321]

Hey, could you try this?
1. Define following custom VBA function (you can google these three words):

Function strrev(strValue As String)
strrev = StrReverse(strValue)
End Function

2. Use it to find the tile size and proceed easily

Tile size:
=RIGHT(A1, FIND(" ", STRREV(A1)) - 1)
The rest:
=LEFT(A1, LEN(A1) - FIND(" ", STRREV(A1)))

 

[Rick Rothstein]

Hi XOR LX & My Answer Is This - Thank you very much for your help.
Unfortunately, the sizes and names are different sizes:
[TABLE="width: 271"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ABBA Azul 31.6x5[/TD]
[/TR]
[TR]
[TD]ABBA Naranja 31.6x5[/TD]
[/TR]
[TR]
[TD]ABBA Pistachio 31.6x5[/TD]
[/TR]
[TR]
[TD]ABBA Rojo 31.6x5[/TD]
[/TR]
[TR]
[TD]AEGYPTUS Mix 10.5x10.5[/TD]
[/TR]
[TR]
[TD]AEGYPTUS Tebe Noce 16.3x16.3[/TD]
[/TR]
[TR]
[TD]AEGYPTUS Tebe Noce 32.7x32.7[/TD]
[/TR]
[TR]
[TD]AEGYPTUS Tebe Noce 49x16.3[/TD]
[/TR]
[TR]
[TD]AEGYPTUS Tebe Noce 49x32.7

The formula =LEFT(A1,LEN(A1)-6) works well for the name of the tiles so is there one for the sizes instead?

Thanks again.[/TD]
[/TR]
</tbody>[/TABLE]

Assuming you data starts in cell A1, put these formulas in the indicated cells and copy down...

B1: =LEFT(A1,LEN(A1)-LEN(C1)-1)

C1: =TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99),99))

 

[HANNC01]

Thank you Rick Rothstein - it works!
Thanks for everyone's help!

Glad I joined this website.

 

[HANNC01]

Hi again...so I have another question about this work.
On the labels, I'm trying to get the code, name, size and price per m2 but I currently have the code, name, size and price per tile. Is there any way I can make it calculate the size to find out how many are in a metre and then work out the price? Because at the moment I'm doing all that manually.

Thanks,
Hannah

 

[HANNC01]

my edit time ran out -

Hi again...so I have another question about this work.
On the labels, I'm trying to get the code, name, size and price per m2 but I currently have the code, name, size and price per tile. Is there any way I can make it calculate the size to find out how many are in a metre and then work out the price? Because at the moment I'm doing all that manually.

COLUMN A COLUMN B COLUMN C COLUMN D COLUMN F
[TABLE="width: 880"]
<tbody>[TR]
[TD="class: xl65, width: 49"]GSD3659[/TD]
[TD="class: xl65, width: 187"][/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl70, width: 245"]VINTAGE Wood 600x15[/TD]
[TD="class: xl68, width: 93"]600x150[/TD]
[TD="class: xl71, width: 92"]£1.61[/TD]
[TD="class: xl69, width: 75"] [/TD]
[TD="class: xl67, width: 75"]
M2


[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to get this:
GSD3659 VINTAGE WOOD 600X150 £1.61 =600X150 TILES GOES INTO A METRE SQUARED 11.11 TIMES SO 11.11*£1.61 <---(PRICE OF ONE TILE) = £17.88M2

Thanks,
Hannah

 

[stan423321]

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.