Parse Data

[Pestomania]

I have a dataset that I cannot modify as it is a kick out from a database. In some cases the value has a 0 at the beginning and sometimes it does not. This causes parsing problems.

I have put together the "current data return" and "the correct data" in the below data. How can I write a formula that will parse the data to ensure the 0# in the center is the floor and everything before is the location and everything after is the post?

ASSETS by BUSUNIT.xlsx
	A	B	C	D	E	F	G	H	I
1	Data	Location	Floor	Post		Data	Location	Floor	Post
2	03224R01BB26	03224R	01	BB26		03224R01BB26	03224R	01	BB26
3	03224R01N13	03224R	01	N13		03224R01N13	03224R	01	N13
4	02278M02A10	02278M	02	A10		02278M02A10	02278M	02	A10
5	02278M02B08	02278M	02	B08		02278M02B08	02278M	02	B08
6	03224R01	03224R	01			03224R01	03224R	01
7	03224R01BB32	03224R	01	BB32		03224R01BB32	03224R	01	BB32
8	03224R01G26	03224R	01	G26		03224R01G26	03224R	01	G26
9	5508701B08	550870	1B	08		5508701B08	55087	01	B08
10	5506101C02	550610	1C	02		5506101C02	55061	01	C02
11	5506101D09	550610	1D	09		5506101D09	55061	01	D09
12	CURRENT Data					Correct Data
Sheet1

Cell Formulas
Range	Formula
B2:B11,G2:G8	B2	=LEFT(A2,6)
C2:C11,H2:H8	C2	=MID(A2,7,2)
D2:D11,I2:I8	D2	=MID(A2,9,5)

 

[Anthony47]

Maybe, in B2

=LET(sStr,A2,SeqA,SEQUENCE(LEN(sStr)),zPos,(IF(CODE(MID(sStr,SeqA,1))=48,SeqA,0)),kPos,IF(MAX(zPos)>7,LARGE(zPos,2),LARGE(zPos,1)),HSTACK(LEFT(sStr,kPos-1),MID(sStr,kPos,2),TRIM(MID(sStr&"  ",kPos+2,99))))

Then copy down

It works in the combinations you shown, not sure it will work in ALL the possible combinations. I assumed (based on some of your words) that floor ranges from 00 to 09

 

[Pestomania]

It worked in all except for 85 cases because their formatting was completely different.

I appreciate the help!

 

[Anthony47]

I hope 85 is a small minority of the cases

 

[Pestomania]

I hope 85 is a small minority of the cases

Out of 15,835, I'll take it

 

[Anthony47]

If these 85 have all the same style maybe we can fix the situation

 

[Pestomania]

If these 85 have all the same style maybe we can fix the situation

These are the ones that are broken.

Majority of the issues fall under these categories:

- 6 digits only = building # only
- OUTSIDEF18:
building: Outside
Column: F18
- 03187SB1U05
Building: 03187S
Floor: B1
Column: U05

03217L
02278MM1B01
OUTSIDEF15
OUTSIDEF14
OUTSIDEF14
OUTSIDEF14
OUTSIDEF14
OUTSIDEF13
OUTSIDEF13
OUTSIDEF13
OUTSIDEF13
OUTSIDEF13
02278MM1A01
02278MM1A01
OUTSIDEF13
OUTSIDEF13
OUTSIDEF13
OUTSIDEF13
OUTSIDEF13
OUTSIDEF13
OUTSIDEF13
OUTSIDEF13
02278MM1B02
OUTSIDEF13
02278MM1B02
OUTSIDEF13
03191MB1D02
03191MB1B02
03191MB1E03
03191MB1D02
02297F
03212T
02319K
03225Q
01198D
04118F
02293N
OUTSIDEJ22
03187SB1X03
03238LM1H14
03238LM1H13
03238LM1H12
03187SB1U05
03187SB1X06
03225N
03187SB1M05
02278M
02314J
03222O
01283FRF
02294N
03199T
02297FB1L31
02297FB1E23
02297FB1N31
OUTSIDEA17
OUTSIDEA17
OUTSIDEA17
OUTSIDEA17
OUTSIDEA17
OUTSIDEF16
OUTSIDEA17
OUTSIDEF17
OUTSIDEA17
OUTSIDEA17
OUTSIDEF18
OUTSIDEF18
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
02278MRF
OUTSIDEA17
OUTSIDEA17

 

[Anthony47]

Try

=LET(aStr,A2,sStr,IF(LEFT(aStr,7)="OUTSIDE",SUBSTITUTE(aStr,"OUTSIDE","OUTSIDE00"),aStr),SeqA,SEQUENCE(LEN(sStr)),zPos,(IF(CODE(MID(sStr,SeqA,1))=48,SeqA,0)),kPos,IF(MAX(zPos)>7,LARGE(zPos,2),LARGE(zPos,1)),IF(LEN(aStr)>6,IF(kPos<6,{"**","**","**"},HSTACK(LEFT(sStr,kPos-1),MID(sStr,kPos,2),TRIM(MID(sStr&"  ",kPos+2,99)))),aStr))

This should fix (I guess) the condition when string is 6 or less digits long, and the OUTSIDE position.
Conditions like 03187SB1U05 create an output in the form ** \ ** \ ** so they can be identified and manually fixed; indeed I don't see a rule that can be used to deal with that condition (the location may be 5 or 6 digits, the Column may be 3 or 4: quite hard to guess where Floor starts from)

 

[Pestomania]

Try

=LET(aStr,A2,sStr,IF(LEFT(aStr,7)="OUTSIDE",SUBSTITUTE(aStr,"OUTSIDE","OUTSIDE00"),aStr),SeqA,SEQUENCE(LEN(sStr)),zPos,(IF(CODE(MID(sStr,SeqA,1))=48,SeqA,0)),kPos,IF(MAX(zPos)>7,LARGE(zPos,2),LARGE(zPos,1)),IF(LEN(aStr)>6,IF(kPos<6,{"**","**","**"},HSTACK(LEFT(sStr,kPos-1),MID(sStr,kPos,2),TRIM(MID(sStr&"  ",kPos+2,99)))),aStr))

This should fix (I guess) the condition when string is 6 or less digits long, and the OUTSIDE position.
Conditions like 03187SB1U05 create an output in the form ** \ ** \ ** so they can be identified and manually fixed; indeed I don't see a rule that can be used to deal with that condition (the location may be 5 or 6 digits, the Column may be 3 or 4: quite hard to guess where Floor starts from)

Absolutely, I am going to recommend they do a cleanup where if something doesn't exist, put a 00 to at least make everything the same length.