Delimit with multiple characters

[Guidestone]

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.

 

[sheetspread]

If you mean 3 spaces in the before example, something like:

Excel 2010
	A	B	C	D	E
1	06/26/2018 08:09:55, 1.7493,LO	06/26/2018	08:09:55	1.7493	LO
Sheet7

Cell Formulas
Range	Formula
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))

 

[Rick Rothstein]

-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.

 

[sheetspread]

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

Nice, here's a single cell approach:

Excel 2010
	A	B	C	D	E
1	06/26/2018 08:09:55, 1.7493,LO	06/26/2018	08:09:55	1.7493	LO
Sheet7

Cell Formulas
Range	Formula
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)

 

[Rick Rothstein]

Nice, here's a single cell approach:

Excel 2010
	A	B	C	D	E
1	06/26/2018 08:09:55, 1.7493,LO	06/26/2018	08:09:55	1.7493	LO
Sheet7

Cell Formulas
Range	Formula
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.

 

[sheetspread]

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.

 

[jtakw]

Hi,

Just another way, pretty straight forward, nothing fancy:

Book1
	A	B	C	D	E
8	06/26/2018 08:09:55, 1.7493,LO	06/26/2018	08:09:55	1.7493	LO
Sheet121

Cell Formulas
Range	Formula
B8	=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE($A8,","," "))," ",REPT(" ",100)),COLUMNS($B8:B8)*100-99,100))

Formula copied down and across as needed.