extracting data from a text string of varying length

[Guest]

is there anyway to get a computed value for the square inches in the
following cell:

LightJet Mural - from Provided File (8.5X11) Normal

I am trying to compute usage from parts that are named with the dimension
embedded in the description - and the dimensions varies with each
description. the dimension is always embedded as shown (widthxheight). the
text string surrounding the numerical data vaires in length

thanks for your collective wisdom, excel gurus...

 

[Don Guillett]

try using

instr
x=instr(string,
y=instr(
' A textual comparison starting at position 4. Returns 6.
MyPos = Instr(4, SearchString, SearchChar, 1)

 

[Dave Peterson]

First, is the only portion of the description that has parentheses () the
dimensions?

If yes, then I'd use some helper columns to extract the beginning (open
parenthesis), the middle (the X) and the end (close parenthesis).

Say your data is starts in A2.
In B1, put (
In C1, put X
In D1, put )
In E1, put Length
In F1, Put Width
In G1, Put Area

Then in B2: =SEARCH($B$1,A2,1)
In C2, put: =SEARCH($C$1,A2,B2)
In D2, put: =SEARCH($D$1,A2,C2)
In E2, put: =IF(COUNT(B22)<>3,"Error",--MID(A2,B2+1,C2-B2-1))
In F2, put: =IF(COUNT(B22)<>3,"Error",--MID(A2,C2+1,D2-C2-1))
In G2, put: =IF(COUNT(E2:F2)<>2,"Error",E2*F2)

All this will break if you have multiple ()'s in your string. Like:
LightJet Mural (Blue) - from Provided File (8.5X11) Normal

And will get worse if you have:
LightJet Mural (Blue) - from Provided File (8.5X11) Normal (ASCII)

====
You can hide those helper columns if you want.

 

[Guest]

sorry, don.

i am not savvy enough to know how to use what you have supplied.

please explain further so i can test it.

thx for the reply.

andy

 

[Guest]

You're going to have to use a combination of MID() & Search() or FIND()
functions

From the help file:

FIND(find_text,within_text,start_num)

FIND finds one text string (find_text) within another text string
(within_text), and returns the number of the starting position of find_text,
from the first character of within_text. You can also use SEARCH to find one
text string within another, but unlike SEARCH, FIND is case sensitive and
doesn't allow wildcard characters.

Assuming the text entry is in A1, then
=SEARCH("(",A1,1) or =FIND("(",A1,1) tells you where the first "(" appears
in the description (position 37 in your example, so the 8.5 STARTS at
position 38.

Next you want to find the "X." You want to find the first X appearing after
the first "("
=SEARCH("x",A1,37) or =FIND("x",A1,37) tells us the X appears in position 41

Now you know that the first dimension (8.5) occupies positions 38, 39, and
40, so you can extract it from the text with:

=MID(A1,39,3)

Follow similar steps to extract the second dimension