Extract text of variable length from string (fixed start position)

[ajarle]

I have cells where I would like to extract the number before the x and after the x in two different columns:

Viewport: 320x460

Viewport: 1370x670

Viewport: 1920x960

Viewport: 1920x980

Viewport: 1920x990

Viewport: 1370x640

Viewport: 1920x960

Viewport: 380x560

<colgroup><col style="width: 139px"></colgroup><tbody>
</tbody>

The numbers are are either three our four digits

 

[gaz_chops]

What is in the cell
Viewport: 380x560
or
380x560

 

[XOR LX]

Hi.

If the "x" in question is always the only occurrence of that letter in the string:

First Number:

=-LOOKUP(1,-MID(A1,FIND("x",A1)-4,{1,2,3,4}))

Second Number:

=0+MID(A1,FIND("x",A1)+1,4)

Regards

 

[ajarle]

The whole text: "Viewport: 380x560".

 

[Special-K99]

First number
=MID(A1,FIND(" ",A1)+1,FIND("x",A1)-FIND(" ",A1)-1)

Second number
=RIGHT(A1,LEN(A1)-FIND("x",A1))

 

[ajarle]

I am using Google sheets. I don't have the Lookup formula there

Hi.

If the "x" in question is always the only occurrence of that letter in the string:

First Number:

=-LOOKUP(1,-MID(A1,FIND("x",A1)-4,{1,2,3,4}))

Second Number:

=0+MID(A1,FIND("x",A1)+1,4)

Regards

 

[ajarle]

The second number works perfectly. Thank you!

I get a #Value error using this formula (semicolons are the Norwegian equivalent of commas):

=MID(B2;FIND(" ";B2)+1;FIND("x";B2)-FIND(" ";B2)-1)

 

[XOR LX]

I am using Google sheets. I don't have the Lookup formula there

You should be ok changing it to HLOOKUP here:

=-HLOOKUP(1,-MID(A1,FIND("x",A1)-4,{1,2,3,4}),1)

Regards

 

[ajarle]

Thanks, but I get an #ERROR using this:

=-HLOOKUP(1;-MID(B2;FIND("x";B2)-4;{1,2,3,4});1)

You should be ok changing it to HLOOKUP here:

=-HLOOKUP(1,-MID(A1,FIND("x",A1)-4,{1,2,3,4}),1)

Regards

 

[ajarle]

I am so sorry Special-K99. I was referencing the wrong cells. Both solutions work perferctly. Thank you very much!

First number
=MID(A1,FIND(" ",A1)+1,FIND("x",A1)-FIND(" ",A1)-1)

Second number
=RIGHT(A1,LEN(A1)-FIND("x",A1))