String Manipulation

  • Thread starter String Manipulation
  • Start date
S

String Manipulation

I have data imported from accounting software that I'm working with. What I
need is to have a column that diplays just the length of the part off the end
of the description. Lengths are in formats like 20-1/4, 4, 5-1/4. They are
always preceded by one or more spaces and are always at the end of the
description. Can anyone help? Following are two examples of descriptions
06530FB0325_SP MDP REC PCH 155 3-1/4
12810FB46_SP MDP LO STD PCH 155 46
Q5478MB/W075_DPBSP REC RAL6027 155 7-1/2
 
D

Dave O

There are a number of string parsing functions you can use, such as
MID, LEFT, and RIGHT. All of these depend on you knowing where the
desired columns are. In your examples, the lengths are always at the
end, but the lengths are a different number of columns: for instance,
the second one is 46 (2 columns) and the third is 7*1/2 (5 columns).

To make this exact, we need to find an element that is common to each
row *and* close to the length portion of the string. I see the value
155 on each line- if that is common to EVERY line, you could use this
formula
=MID(A2,FIND("155 ",A2,1)+4,LEN(A2))
.... where the imported string is in cell A2.

Is that 155 on every line?
 
R

Ron Rosenfeld

On Wed, 30 Nov 2005 13:31:02 -0800, "String Manipulation" <String
I have data imported from accounting software that I'm working with. What I
need is to have a column that diplays just the length of the part off the end
of the description. Lengths are in formats like 20-1/4, 4, 5-1/4. They are
always preceded by one or more spaces and are always at the end of the
description. Can anyone help? Following are two examples of descriptions
06530FB0325_SP MDP REC PCH 155 3-1/4
12810FB46_SP MDP LO STD PCH 155 46
Q5478MB/W075_DPBSP REC RAL6027 155 7-1/2
Click to expand...


=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)


--ron
 
R

Ron Coderre

See if this works for you:
=RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&(LEN(A1)))),1),0)-1)

Commit that array formula by holding down [Ctrl] and [Shift] when you press
[Enter]


Does that help?

***********
Regards,
Ron
 
Top