Extract data from middle of a cell

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
167
Office Version
  1. 365
Platform
  1. Windows
Hi everyone. I'm trying to do a formula that will extract data from the middle of a cell. For example I have data like this in cells:
Toms Roofing B016321 Z001623
There is a company name, Company ID, and Service Code. I need to be able to split these into 3 columns. Since the company name, company ID, and service code are not a set # of characters (ex. Company Code could be B0000000651 for one and maybe B00541 for another) I can't use the text to columns feature. If anyone can help with a formula I would appreciate it. I've tried several and can't get it to work correctly with all the rows on the worksheet.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
I suggest that you update your Account details (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’)

Does this do what you want?
20 05 13.xlsm
ABCD
1
2Toms Roofing B016321 Z001623Toms RoofingB016321Z001623
3Ace Carpentry and Cabinets B123 Z6567Ace Carpentry and CabinetsB123Z6567
Split Columns
Cell Formulas
RangeFormula
B2:B3B2=LEFT(A2,LEN(A2)-LEN(C2&D2)-2)
C2:C3C2=TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",20)),40),20))
D2:D3D2=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",20)),20))
 
Upvote 0
I suggest that you update your Account details (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’)

Does this do what you want?
20 05 13.xlsm
ABCD
1
2Toms Roofing B016321 Z001623Toms RoofingB016321Z001623
3Ace Carpentry and Cabinets B123 Z6567Ace Carpentry and CabinetsB123Z6567
Split Columns
Cell Formulas
RangeFormula
B2:B3B2=LEFT(A2,LEN(A2)-LEN(C2&D2)-2)
C2:C3C2=TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",20)),40),20))
D2:D3D2=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",20)),20))
Peter, Thank you so much for your help. This worked for most of the data. There are some cells where it looks like there are 2 spaces between the data, like B00002 (TWO SPACES) W999999, so on those ones it's not catching the middle data. Other than that it works great. I appreciate your help.
 
Upvote 0
There are some cells where it looks like there are 2 spaces between the data
Assuming they are normal spaces, try these instead

20 05 13.xlsm
ABCD
1
2Toms Roofing B016321 Z001623Toms RoofingB016321Z001623
3Ace Carpentry and Cabinets B123 Z6567Ace Carpentry and CabinetsB123Z6567
Split Columns
Cell Formulas
RangeFormula
B2:B3B2=TRIM(LEFT(TRIM(A2),LEN(TRIM(A2))-LEN(C2&D2)-2))
C2:C3C2=TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",20)),40),20))
D2:D3D2=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",20)),20))
 
Upvote 0
Assuming they are normal spaces, try these instead

20 05 13.xlsm
ABCD
1
2Toms Roofing B016321 Z001623Toms RoofingB016321Z001623
3Ace Carpentry and Cabinets B123 Z6567Ace Carpentry and CabinetsB123Z6567
Split Columns
Cell Formulas
RangeFormula
B2:B3B2=TRIM(LEFT(TRIM(A2),LEN(TRIM(A2))-LEN(C2&D2)-2))
C2:C3C2=TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",20)),40),20))
D2:D3D2=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",20)),20))

That worked perfect. I'm not sure I understand the formulas but it worked. LOL. I really appreciate your help.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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