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

Argonous

New Member
Joined
Sep 23, 2004
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
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.

Computers.jpg
Remove.jpg


Let me know what you can come up with.

Thanks everyone!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
I show you my proposal, which includes an auxiliary column.

The data in your "Data" sheet:
Dante Amor
AB
1BldgRemove
23A-CONF
33B-DECOM
43EDS-
53HLABTRK
65CX
7CH
Data


The data in your sheet, in column "B" I put the Auxiliary column:
Dante Amor
ABCDE
1NameName AuxBldgRoomLoc
23420-013420-01---342001
33A0100-013A0100-013A010001
43A2106-A1-13A2106-A1-13A2106A1-1
53A2108LABTRK-013A2108-013A210801
63B111-023B111-023B11102
73E5122-DECOM3E51223E5122 
83E5123-CONF3E51233E5123 
93H1060-013H1060-013H106001
103H1060-023H1060-023H106002
115C21OD-015C21OD-015C21OD01
125C210E-015C210E-015C210E01
13DS-3A1175-013A1175-013A117501
14XCH244A-01CH244A-01CH244A01
Sheet
Cell Formulas
RangeFormula
B2:B14B2=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:C14C2=IFERROR(VLOOKUP(LEFT(B2,2),Data!$A$2:$A$7,1,0),"---")
D2:D14D2=TRIM(LEFT(SUBSTITUTE(MID(B2,IFERROR(SEARCH(C2,B2)+2,1),99),"-",REPT(" ",99)),99))
E2:E14E2=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.
 
Upvote 0
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’)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top