Extract Characters from a Text String of Varying Length

[JM360]

I’m trying to come up two formulas that can extract characters from the middle of a string of text of varying lengths. Here is an example-
A1: 1234 309 Embankment Cu. Yds. 217
B1: 4567 587 12" Conduit, Type B, As Per Plan Lin. Ft. 14
C1: 4321 5432 Pull Box Adjusted to Grade - Contingency Ea. 1
D1: 7654 Spec. Power Distribution Cabinet, Complete (Item 4894) Lump Sum 1

My goal:
[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD="align: center"]ID[/TD]
[TD="align: center"]ITEM[/TD]
[TD="align: center"]DESCRIPTION[/TD]
[TD="align: center"]UNIT[/TD]
[TD="align: center"]QTY[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]309[/TD]
[TD]Embankment[/TD]
[TD]Cu. Yds.[/TD]
[TD]217[/TD]
[/TR]
[TR]
[TD]4567[/TD]
[TD]589[/TD]
[TD]12” Conduit, Type B, As Per Plan[/TD]
[TD]Lin. Ft.[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]4321[/TD]
[TD]5432[/TD]
[TD]Pull Box Adjusted to Grade – Contingency[/TD]
[TD]Ea.[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7654[/TD]
[TD]Spec.[/TD]
[TD]Power Distribution Cabinet, Complete (Item 4894)[/TD]
[TD]Lump Sum[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I would like to formulate the Description and Unit columns. I’ve already have formulas set up for ID, ITEM, and QTY using =LEFT, =MID, and =TRIM/RIGHT/SUBSTITUTE/REPT formulas respectively.

Here is what I’ve noticed with the dataset:
ID is consistently four digits
ITEM varies in length but always has one set of characters surrounded by a space on each side
DESCRIPTION varies in length and in character usage
UNIT while different these are consistent in use
QTY numbers vary in length but always have one set of characters surrounded by a space on each side

Is this possible without VBA? Thank you in advance.

 

[NiMip]

I've come up with a bit of a clunky way I guess... someone may have a cleaner method. Mine assumes a couple of things:

• You already have ID, ITEM and QTY figured out, as you've stated you have
• You have a complete register of the UNITs you're expecting to find (I'll call this unitRange), and which are numbered consecutively in another column (I'll call this numUnitRange)

To find the UNIT, you could use the formula:

{=INDEX(unitRange,MAX(IF(ISNUMBER(SEARCH(unitRange,"String")),numUnitRange)))}​

Notice the curly brackets: this is an array formula and requires control+shift+enter.

You could then use something like this to remove everything but the DESCRIPTION:

=MID("String",LEN(ID&ITEM)+3,LEN("String")-LEN(ID&ITEM&UNIT&QTY)-4)​

The +3 is to compensate for the spaces I'm assuming you've TRIMmed off your ID and ITEM, and move to the first character of the DESCRIPTION, and the -4 is just for the spaces.

It's a bit scenario-specific, but I hope that works for you? Let me know if I need to clarify anything.

 

[JM360]

Thank you NiMip for your reply. I tried your method and it appears to work at first glance. I experimented with a number of clunky ways myself...some involving character and space (" ") counts but the method below worked the best for my scenario.

It requires all UNIT categories are manually entered but since there aren't many unit categories in my data set, utilizing Excel AutoFill and Flash Fill made this a quick chore. Formulas in Columns B, C, D, and F are simply copied down. Set up is as follows:

[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]String Text From PDF[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]ITEM[/TD]
[TD="align: center"]DESCRIPTION[/TD]
[TD="align: center"]UNIT[/TD]
[TD="align: center"]QTY[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1234 309 Embankment Cu. Yds. 217[/TD]
[TD]=LEFT(A2,4)[/TD]
[TD]=TRIM(MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)))[/TD]
[TD]=TRIM(MID(A2,SEARCH(C2,A2)+LEN(C2),SEARCH(E2,A2)-SEARCH(C2,A2)-LEN(C2)))[/TD]
[TD]Cu. Yds.[/TD]
[TD]=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4567 587 12" Conduit, Type B, As Per Plan Lin. Ft. 14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lin. Ft.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4321 5432 Pull Box Adjusted to Grade - Contingency Ea. 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ea.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7654 Spec. Power Distribution Cabinet, Complete (Item 4894) Lump Sum 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lump Sum[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I should note that the above requires that UNITS match the Units referenced in the pasted text in Column-A String Text From PDF and vice versa. For example, if OCR doesn't pick up the first period in one of the PDF rows, one could easily miss "Cu Yds." versus "Cu. Yds"

 

[Peter_SSs]

1. So, do you have a result that you are satisfied with or are you still looking for something better?

2. If still looking, is there a particular reason for disallowing vba?

 

[JM360]

Yes, both approaches above work. The reason I was looking for a non-VBA approach is because this spreadsheet will be shared with multiple novice Excel users. Less 'technical' steps, the better.

 

[Peter_SSs]

Here is another formula approach that may be some use. The list of possible units (with no dots) is placed somewhere - column J for me.
Each of the formulas is copied down.

Notes:
a) You mentioned about OCR possibly not picking up all the dots so I have included 4 similar rows with various combinations of the dots in the units.
b) I have used a much smaller number of spaces in the QTY formula than you did. The larger number you use, the more processing has to be done & I figured you will not have any final numbers longer than 10 characters. If you might then increase it appropriately.
c) My 'Description' formula will fail (partially at least) if there are multiple dots in the early part of the original text - notice the missing final characters in the last sample. If that needs attention we possibly could reduce that risk a little but I didn't think it worth complicating the formulas further at this stage if it is unlikely to be an issue for you. (We would have a bit more flexibility/control over 'dot issues' if we able to introduce VBA )

Book1
	A	B	C	D	E	F	G	H	I	J
1	Data	ID	ITEM	DESCRIPTION	UNIT	QTY				Cu Yds
2	7654 Spec. Power Distribution Cabinet Complete (Item 4894) Lump Sum 1	7654	Spec.	Power Distribution Cabinet Complete (Item 4894)	Lump Sum	1				Cu Yards
3	1234 309 Embankment Cu. Yds. 217	1234	309	Embankment	Cu Yds	217				Ea
4	1234 309 Embankment Cu Yds. 217	1234	309	Embankment	Cu Yds	217				Lin Ft
5	1234 309 Embankment Cu. Yds 217	1234	309	Embankment	Cu Yds	217				Lump Sum
6	1234 309 Embankment Cu Yds 217	1234	309	Embankment	Cu Yds	217
7	4321 5432 Pull Box Adjusted to Grade - Contingency Ea. 1	4321	5432	Pull Box Adjusted to Grade - Contingency	Ea	1
8	4567 587 12" Conduit, Type B, As Per Plan Lin. Ft. 14	4567	587	12" Conduit, Type B, As Per Plan	Lin Ft	14
9	5698 456986 Some description cu yards 203	5698	456986	Some description	Cu Yards	203
10	7654 Spec. Power. Distribution. Cabinet. Complete (Item 4894) Lump Sum 1	7654	Spec.	Power. Distribution. Cabinet. Complete (Item 48	Lump Sum	1
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(A2,4)
C2	=REPLACE(LEFT(A2,FIND(" ",A2,6)-1),1,5,"")
D2	=TRIM(REPLACE(LEFT(A2,LOOKUP(9E+99+307,SEARCH(" "&$J$1:$J$5&" "," "&SUBSTITUTE(A2,".","")&" "))-1),1,LEN(B2&C2)+2,""))
E2	=LOOKUP(9.99E+307,SEARCH(" "&$J$1:$J$5&" "," "&SUBSTITUTE(A2,".","")&" "),$J$1:$J$5)
F2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",10)),10))+0