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