Return text 'around' the x

**SHI.NL** · 05-22-2018, 05:02 AM

Hi,

I am familiar with left, right, mid, len etc. But now I have a column with very inconsistent data and I have to extract the text 'around' the X. So recognize the x and then return what's between spaces.

E.g.

Hello Pocket Hr Yes medium 90x210 >>> return >>> 90x210
Pillow according to 161x47x8 according to rules >>> return >>> 161x47x8
pocket, AA10, 1.6/1.8, Medium, 100x200 >>> return >>> 100x200
Cover 180x200, core 19 >>> return >>> 180x200
Pocket 40 HALLO 90x210x22 (2x 4,5cm) ATTENTION HAS EXCEPTION >>> return >>> 90x210x22

Hope someone know how to do the trick! Thanks.

**JohnTopley** · 05-22-2018, 05:33 AM

so expected results are

90 and 120 from 90x120

**SHI.NL** · 05-22-2018, 05:39 AM

Hello Pocket Hr Yes medium 90x210 should return 90x120
for example.

**ImranBhatti** · 05-22-2018, 05:49 AM

try this
in the adjacent cell and drag down

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))),LEN(TRIM(A1))))

**SHI.NL** · 05-22-2018, 05:57 AM

Thanks Imran, unfortunately this only works if the sizes are to be found on the right, now when they are in the middle.... Thanks though.

**ImranBhatti** · 05-22-2018, 06:03 AM

Sorry I thought the result is the last word
try this

add a helper column say B
type this formula in B1
=A1&" "
then in C1 type

= RIGHT(SUBSTITUTE(TRIM(LEFT(B3,FIND(" ",B3,FIND("x",B3)))&" "), " ", REPT(" ", LEN(TRIM(LEFT(B3,FIND(" ",B3,FIND("x",B3)))&" ")))),LEN(TRIM(LEFT(B3,FIND(" ",B3,FIND("x",B3)))& " ")))

and copy down

**samba_ravi** · 05-22-2018, 06:10 AM

Try below array formula in B1 and copy towards down, assume that your data in Cell A1

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Return text 'around' the x

LinkBack

Thread Tools

Rate This Thread

Display

Return text 'around' the x

Re: Return text 'around' the x

Re: Return text 'around' the x

Re: Return text 'around' the x

Re: Return text 'around' the x

Re: Return text 'around' the x

Re: Return text 'around' the x

Thread Information

Users Browsing this Thread

Similar Threads

If cell contains certain text and certain text then return predefined text

[SOLVED] How to match a cell with text with a range of text and return own text

Formula to find and return text string within a cell full of text strings

[SOLVED] function IF to return text based if text contains exact text

Find a text and return the sum of values in adjacent cells matching the text in range

If cell contains text1, text2 or text 3 return this text, otherwise return X

Need to find text in a named range, and return part of the text to a single cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions