Delimit with multiple characters

Guidestone

New Member
Joined
Jul 5, 2018
Messages
14
I need help with an equation I've been working on for a few days now. I need the equation to take the information that is entered in one cell and split it into 4 parts in for other cells next to it while removing the extra characters. It also has to do this sequentially one row after another for at least 110 rows. I cannot use the delimit tool and need an equation because the data has to be generated automatically as the information is deposited in the left column.

-The incoming data will have the same number of character each time but the characters will change
-The data needs to be separated into four cells: Date, Time, Dimension, Signal
-All of the spaces and commas will need to be removed, while keeping the other characters with their respective data

The data coming in will look like this in the left hand cell:

06/26/2018 08:09:55, 1.7493,LO

It needs to be separated to look like this in 4 cells in a single row:

06/26/2018 08:09:55 1.7493 LO

Here is the equation I've been working with so far with no success:

=TRIM(MID(SUBSTITUTE($A8,delim,REPT(" ",LEN(A8))),(N-1)*LEN(A8)+1,LEN(A8)))

Thanks in advance for any help you can provide!


EDIT: I just noticed that when I posted this, the forum removed the two extra spaces between "08:09:55," and "1.7493". There should be 3 spaces total between them.
 
Last edited by a moderator:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
If you mean 3 spaces in the before example, something like:


Excel 2010
ABCDE
106/26/2018 08:09:55, 1.7493,LO06/26/201808:09:551.7493LO
Sheet7
Cell Formulas
RangeFormula
B1=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,","," ")," ",REPT(" ",100)),100))
C1=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,","," ")," ",REPT(" ",100)),100,100))
D1=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,","," ")," ",REPT(" ",100)),500,100))
E1=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,","," ")," ",REPT(" ",100)),600,100))
 
Upvote 0
-The incoming data will have the same number of character each time but the characters will change
Here is the equation I've been working with so far with no success:

=TRIM(MID(SUBSTITUTE($A8,delim,REPT(" ",LEN(A8))),(N-1)*LEN(A8)+1,LEN(A8)))
Assuming that each cell in Column A with data has the same number of characters as you stated it will above, and assuming your data starts on cell A8 as your posted (attempted) formula shows, select cells B8:E8 and put this formula in the Formula Bar

=MID(A8,{1,12,20,29},{10,8,8,2})

Then commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself. After you have done this, copy the range B8:E8 down to the bottom of your data.
 
Last edited:
Upvote 0
=MID(A8,{1,12,20,29},{10,8,8,2})

Nice, here's a single cell approach:


Excel 2010
ABCDE
106/26/2018 08:09:55, 1.7493,LO06/26/201808:09:551.7493LO
Sheet7
Cell Formulas
RangeFormula
B1=MID($A$1,INDEX({1,12,22,31},COLUMN(A1)),INDEX({10,8,8,2},COLUMN(A1)))


(easily adjusted if you don't want the extra spaces)
 
Last edited:
Upvote 0
Nice, here's a single cell approach:


Excel 2010
ABCDE
106/26/2018 08:09:55, 1.7493,LO06/26/201808:09:551.7493LO
Sheet7
Cell Formulas
RangeFormula
B1=MID($A$1,INDEX({1,12,22,31},COLUMN(A1)),INDEX({10,8,8,2},COLUMN(A1)))


(easily adjusted if you don't want the extra spaces)
Nice as well! I wonder which is more efficient... your normally-entered formula with five function calls or my array-entered formula with one function call? I know there is an overhead for array-entered formulas, but I have never been clear as to how much.
 
Upvote 0
Your way seems better even with many more rows/parsing points. Dragging one cell avoids the "You cannot change part of an array" error, but that may not be needed here.
 
Last edited:
Upvote 0
Hi,

Just another way, pretty straight forward, nothing fancy:


Book1
ABCDE
806/26/2018 08:09:55, 1.7493,LO06/26/201808:09:551.7493LO
Sheet121
Cell Formulas
RangeFormula
B8=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE($A8,","," "))," ",REPT(" ",100)),COLUMNS($B8:B8)*100-99,100))


Formula copied down and across as needed.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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