[Closed] EXCEL HELP - Please

I assume the column is variable length?

Is the size always the last characters of the line, or in the midst of other text?

as joao says, do you have a common form factor for the cell content in B? Got an example?

Need more info on the format of data in column B to help.

i.e.

"Colour:Red, Material:Cotton, Product:TShirt, Size:XL, Logo:Red" etc

or

RedCotTShXLRed

or

"This tastefully decorated gimp mask has diamanté highlights to the ball gag fittings that offsets the red PVC beautifully. This comes in size XL"

You can get one size from the string. I think...
=MID(A2,FIND("m",A2),1) where you are looking for M

You can get one size from the string. I think...
=MID(A2,FIND("m",A2),1) where you are looking for M"

..but of course looking for "L" will find it in a description having L,XL,XXL,Lemon,Latex,Lingerie

Come on rottwieler, give us some rows to look at!

Equipe Short Sleeve Jersey Black S
Equipe Short Sleeve Jersey Black M
Equipe Short Sleeve Jersey Black L
Equipe Short Sleeve Jersey Black XL
Equipe Short Sleeve Jersey Black XXL

size is not always in the same place but usually at the end

size is not always in the same place but usually at the end

unfortunately it's a case of a miss is as good as a mile with spreadsheet functions 😉

Will see if I can come up with something that can work with that..back in a tick...

If it's ALWAYS at the end then you can use this:

=IF(RIGHT(A1,4)="XXXL","XXXL",IF(RIGHT(A1,3)="XXL",
"XXL",IF(RIGHT(A1,2)="XL","XL",IF(RIGHT(A1,2)="XS",
"XS",IF(RIGHT(A1,1)="S","S",IF(RIGHT(A1,1)="M","M",
IF(RIGHT(A1,1)="L","L","Unknown")))))))

If it's not always at the end then you'll never be able to stop something like "Equipe Short Sleeve" looking like a Small

If it's not always at the end then you'll never be able to stop something like "Equipe Short Sleeve" looking like a Small

Unless there are a limited number of words you can choose to ignore like:

Equipe
Short
Long
Sleeve
Jersey
Blank
White

If you can list all the words which aren't a size, eliminate them from the text, you should be left with just the size. I'd do that using search & replace in a text editor if it's a one-off.

this will return the last "thing" in the cell, regardless of length or numbers of spaces.

But if the last thing isnt the size you want, you will need to go and fiddle with it manually

=RIGHT(B1,LEN(B1)-FIND("|",SUBSTITUTE(B1," ","|",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))

"This tastefully decorated gimp mask has diamanté highlights to the ball gag fittings that offsets the red PVC beautifully. This comes in size XL"

I think you'll find that those ARE available in XXL if you know which websites to search. 😳

I vote Stoner's solution more elegant.

Laura is good with excel..... 🙂

Would it not be a start to break the cell up into multiple cells? Then you can hunt out the size even if its in the wrong column. Surely with some clever if statements you could get some results?

Searching for L would just be mental!

Does it need to be bulletproof, used regularly etc? Could you just try it with the suggestion and see how many exceptions you get? You may find there are few enough to handle manually.

d'Oh. thats a good point pealy, use Data>Text to Columns. Delimited by Space, to break it all up, then use a series of MATCH functions to find the size...

Just spoke to PO on the phone, and I think we sorted it

text to columns, with space as the delimiter

so you end up with five words, each in a different column and the size in the sixth column

now concatenate the first four columns reinserting spaces with text " "

=CONCATENATE(A3," ",B3," ",C3," ",D3," ",E3)

giving you two columns with words in one, and sizes in the other.

giving you two columns with words in one, and sizes* in the other.

* as long as the size is always at the end.. Isn't that where we came in?

Cheers guys i think we have cracked it thanks for all your helps