Find matching string and get string of data in offset cell from raw dataset

[robystar]

Attached is a screen cap of the imported dataset. Please note how the data is grouped with multiple labels and values in a single cell.
I need to find the match JSN code which is located in a single cell along with miscellaneous data. The "JSN" text has (4) spaces after followed by the (5) digit alphanumeric JSN code. Once this code is located, using a matching reference cell , I need to obtain the "BTU / Hour" data. This data is located is single cell along with miscellaneous data as well. The "BTU / Hour" text here is followed by (5) spaces. Unfortunately, the "BTU / Hour" data is inconsistent. It's sometime 0, blank, or a (3) or (4) digit number. Blank should be interpreted as 0. There are about 2000 sets of data that I need to extract this information from. Any help would be greatly appreciated.

 

[awoohaw]

It would be very helpful to please post this using the xl2bb add in. See the link below.
Are the visible borders also indicative of merged cells (can you remove borders and then display gridlines?
I'm curious if you are able to get this data as a flat file?

 

[robystar]

Sample of one dataset in the RAWDATA.

TEST_JSN DATA Extractor.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
2	JSN A0900 Name Relay Rack, Aluminum
3	Logistical Category (LOGCAT)											Fund Source					Weights and Measures (Typical Only)
4	Army A | Contractor Furnished, Contractor Installed																Weight (lbs)	39		Weight (kgs)						18
5	Navy C | Government Furnished, Government Installed																Height (in)	84		Height (cen)						213
6	USAF C | Government Furnished, Government Installed																Width (in)	21		Width (cen)						53
7	VA . |																Depth (in)	15		Depth (cen)						38
8	IHS . |																NSN
9	Utilities NOTE: Values shown are based on standard typicals used for programming purposes. The contractor remains responsible to develop and coordinate the design / construction in accordance with actual equipment selection. Utility1: Plumbing . | Volts1 0 Utility2: Electrical . | Volts2 0 Utility3: Med Gas . | Hertz 0 Utility4: Misc Gas . | Hertz Dep 0 Utility5: Non-Med Gas . | Hertz Switch 0 Utility6: Misc . | BTU / Hour 589 Phase1 0 Amps1 0 Watts1 Phase2 0 Amps2 0 Watts2
RAWDATA

 

[robystar]

If you have a preference for document sharing I think that way would work best. Please let me know.

 

[awoohaw]

thanks, So you have 18000 rows of this?

Assumptions.
1. there are no blank rows between records.
2. JSN ID is always 5 characters. and always 5 spaces from JSN to the ID #.
3.. first record starts on row2 (if not you need to adust the MOD formulas).
4. the BTU Number find is somewhat haphazard, you'll need to test and adjust the MID formula for your different scenarios. (and during my testing I may have put in or removed some spaces from your original).
5. Copy the formula down your 18000 rows!

Edited:

mr excel 9.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2	A0900	589	JSN A0900 Name Relay Rack, Aluminum
3			Logistical Category (LOGCAT)											Fund Source					Weights and Measures (Typical Only)
4			Army A | Contractor Furnished, Contractor Installed																Weight (lbs)	39		Weight (kgs)						18
5			Navy C | Government Furnished, Government Installed																Height (in)	84		Height (cen)						213
6			USAF C | Government Furnished, Government Installed																Width (in)	21		Width (cen)						53
7			VA . |																Depth (in)	15		Depth (cen)						38
8			IHS . |																NSN
9			Utilities NOTE: Values shown are based on standard typicals used for programming purposes. The contractor remains responsible to develop and coordinate the design / construction in accordance with actual equipment selection. Utility1: Plumbing . | Volts1 0 Utility2: Electrical . | Volts2 0 Utility3: Med Gas . | Hertz 0 Utility4: Misc Gas . | Hertz Dep 0 Utility5: Non-Med Gas . | Hertz Switch 0 Utility6: Misc . | BTU / Hour 589 Phase1 0 Amps1 0 Watts1 Phase2 0 Amps2 0 Watts2
Sheet2

Cell Formulas
Range	Formula
A2:A9	A2	=IF(MOD(ROW(A2)-2,8)<>0,"", MID(C2,FIND("JSN",C2)+7,5) )
B2:B9	B2	=IF(MOD(ROW(A2)-2,8)<>0,"", IFERROR(1*(TRIM(MID(C9,FIND("BTU / Hour",C9)+14,5))),0) )

 

[awoohaw]

@robystar , a few more things. I inserted columns as you can see. to place the formulas there. If you put the formulas in a different relationship to your first column of data you will need to adjust cell references there as well.

regarding my comment on no spaces between data records. As long as there is consistent spacing you can adjust the divisor in the MOD formula, just add one for each additional row. Since one record has 8 rows, the denominator in my formula is 8.

another assumption. the BTU information is alwasy in the 8 row of data.

another assumption, the JSN NUMBER and the BTU information is always in the same column. (this is why i needed to see the data with no borders so I could see what your cell merging does (merging cells looks good for presentations and reports but is not good for data!).

 

[awoohaw]

If you have a preference for document sharing I think that way would work best. Please let me know.

no need, I think I have what I need. If the solution i've provided doesn't work then maybe i'll need.