GENTS T SHIRT Black 2XL
GENTS T SHIRT Black XL
GENTS T SHIRT Blue Large
GENTS T SHIRT Grey 2XL
Assuming this being on A1 , formula required on B1 to mark Y if A1 has 2XL then on C1 I want formula to pull out colour , the colour will usually be the second last word
What also will be the formula to pull out last word , result on above at d1 to be
2XL
XL
Large
2XL
Hi,
In B1:
Formula:
Please Login or Register to view this content.
In D1:
Formula:Please Login or Register to view this content.
I'll have to get back to you for C1 - I haven't worked out how to extract the 2nd last word yet...
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
For the color
First I made a defined name to give the number of spaces within any value in column A. With the active cell anywhere on row 1,
I defined Spaces = =LEN(Sheet1!$A1)-LEN(SUBSTITUTE(Sheet1!$A1," ",""))
Then for the color
=MID(A1, FIND("^", SUBSTITUTE(A1, " ", "^", Spaces-1))+1, FIND("^", SUBSTITUTE(A1, " ", "^", Spaces))-FIND("^", SUBSTITUTE(A1, " ", "^", Spaces-1))-1)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
A bit cumbersome, perhaps, but returns the second last word, assuming you have Aardigspook's formula in D1.
Formula:Please Login or Register to view this content.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Another option
=TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,LEN(A1)-LEN(D1),LEN(D1)+1,"")," ",REPT(" ",LEN(A1))),LEN(A1)))
Chemist I took yours for the second last word , works
Aardigspook I took both your answers
Thank you all
You're welcome - glad we could help.
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks