Separate and Remove Text from a Cell Based on Data from Another Sheet

[Argonous]

Hello everyone,

I need help with a complicated formula (for me at least). I a trying to separate and remove data from a cell in one sheet based on data in another. I have successfully been able to pull out the Bldg data from the other sheet using INDEX/MATCH if the Bldg code is the first two characters, which in most cases it is. What I am struggling with is getting the desired data in the Room and Loc columns as well as the removal of the "junk" data based on a column in another sheet called Data.

Here is what I need the formula(s) to do:

1. Remove any characters before the building code (DS-, X) designated in the Bldg column of the Data sheet.
2. After step 1, extract the first two characters of the data and place it in the Bldg column (done already with INDEX/MATCH if junk data not present).
3. Split the Loc data from the cell (01, 02, A1-1) and place it in the Loc column.
4. Remove any additional "junk" data as specified in the Remove column of the other sheet called Data.

Data example is below. As you can see, the locations of the data I need to extract/remove are not consistent. I want to avoid using a Macro or VBA, as the user will be recreating this file every 3 months and will need to copy the formulas over to the new workbook and adding data to the Bldg and Remove columns on the Data sheet.

Let me know what you can come up with.

Thanks everyone!

 

[DanteAmor]

I show you my proposal, which includes an auxiliary column.

The data in your "Data" sheet:

Dante Amor
	A	B
1	Bldg	Remove
2	3A	-CONF
3	3B	-DECOM
4	3E	DS-
5	3H	LABTRK
6	5C	X
7	CH
Data

The data in your sheet, in column "B" I put the Auxiliary column:

Dante Amor
	A	B	C	D	E
1	Name	Name Aux	Bldg	Room	Loc
2	3420-01	3420-01	---	3420	01
3	3A0100-01	3A0100-01	3A	0100	01
4	3A2106-A1-1	3A2106-A1-1	3A	2106	A1-1
5	3A2108LABTRK-01	3A2108-01	3A	2108	01
6	3B111-02	3B111-02	3B	111	02
7	3E5122-DECOM	3E5122	3E	5122
8	3E5123-CONF	3E5123	3E	5123
9	3H1060-01	3H1060-01	3H	1060	01
10	3H1060-02	3H1060-02	3H	1060	02
11	5C21OD-01	5C21OD-01	5C	21OD	01
12	5C210E-01	5C210E-01	5C	210E	01
13	DS-3A1175-01	3A1175-01	3A	1175	01
14	XCH244A-01	CH244A-01	CH	244A	01
Sheet

Cell Formulas
Range	Formula
B2:B14	B2	=IFERROR(SUBSTITUTE(A2,INDEX(Data!$B$2:$B$6,MAX(IF(ISNUMBER(SEARCH(Data!$B$2:$B$6,A2)),ROW(Data!$B$2:$B$6)))-ROW(Data!$B$2)+1),""),A2)
C2:C14	C2	=IFERROR(VLOOKUP(LEFT(B2,2),Data!$A$2:$A$7,1,0),"---")
D2:D14	D2	=TRIM(LEFT(SUBSTITUTE(MID(B2,IFERROR(SEARCH(C2,B2)+2,1),99),"-",REPT(" ",99)),99))
E2:E14	E2	=REPLACE(B2,1,FIND("-",B2&"-"),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

---------------
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)