**Question description:**

*This user has given permission to use the problem statement for this
blog.*

How do I create a formula for a column to automatically parse data in a certain way (rather than manually having to redo it whenever I add a new entry to the column)?

Solved by S. A. in 55 mins

This is the chat thread from the real Excelchat help session. It contains no private user
information.

Excelchat Expert
19/07/2018 - 03:01

Hello, I hope you are having a good day. I am here to help with your question.

User
19/07/2018 - 03:01

Thank you

Excelchat Expert
19/07/2018 - 03:01

so is your question specific to google spreadsheets or offline excel?

User
19/07/2018 - 03:01

Offline excel

User
19/07/2018 - 03:02

Do you understand it?

Excelchat Expert
19/07/2018 - 03:02

yes I do

Excelchat Expert
19/07/2018 - 03:02

so to confirm

Excelchat Expert
19/07/2018 - 03:03

lets say you have a sheet with some data on it

User
19/07/2018 - 03:03

right, from notebad and its a series of numbers and letters together that I want parsed

Excelchat Expert
19/07/2018 - 03:04

will there be a delimiter in that data?

Excelchat Expert
19/07/2018 - 03:05

meaning something recognisable to split the data into several cells?

User
19/07/2018 - 03:05

there will not be a delimiter

User
19/07/2018 - 03:05

It would be manual

Excelchat Expert
19/07/2018 - 03:06

could you give me an example of a sample so that I can think of best way for you to handle it?

User
19/07/2018 - 03:07

787X020724 GIV-3 120112 0.3.1.33.1.4.3342:5

User
19/07/2018 - 03:07

I would put divide those values into 7 different columns

User
19/07/2018 - 03:07

Columns labeled A, B, C, D, E

Excelchat Expert
19/07/2018 - 03:08

ok

Excelchat Expert
19/07/2018 - 03:08

let me have a quick look at the example data

User
19/07/2018 - 03:08

And I would like a formula so that everytime I receive or insert new data like that, it automatically inserts it to how I parsed the initial one

Excelchat Expert
19/07/2018 - 03:09

could you help me split this into 7 columns?

Excelchat Expert
19/07/2018 - 03:09

as in how you would like to see it?

User
19/07/2018 - 03:10

Well that's what I would use the parse tool for .. under data..

User
19/07/2018 - 03:10

so I would enter the data like this

Excelchat Expert
19/07/2018 - 03:10

yea

Excelchat Expert
19/07/2018 - 03:10

t me tell you what I am thinking about

Excelchat Expert
19/07/2018 - 03:10

In Microsoft excel, tehre is a way in which you can get data from external source

Excelchat Expert
19/07/2018 - 03:11

its under data, and from text

Excelchat Expert
19/07/2018 - 03:11

then you can select the notepad file from where you are getting the data

Excelchat Expert
19/07/2018 - 03:11

now when you import it, you will have two options Delimited and fixed width

Excelchat Expert
19/07/2018 - 03:12

fixed width lets you manually select until where the first column of the data will be

Excelchat Expert
19/07/2018 - 03:13

otherwise it could be delimited where you can select space, comma etc... to split it into different columns

User
19/07/2018 - 03:13

That makes sense. But would I have to do that everytime I insert a new table?

User
19/07/2018 - 03:13

As opposed to "copy paste" and it automatically separates it where the last table was?

Excelchat Expert
19/07/2018 - 03:13

Yes indeed

Excelchat Expert
19/07/2018 - 03:14

or

User
19/07/2018 - 03:14

I will try it quick

Excelchat Expert
19/07/2018 - 03:14

ou can just select the cell where you would want the next set of data to be pasted

Excelchat Expert
19/07/2018 - 03:14

you can get first table in

Excelchat Expert
19/07/2018 - 03:15

t's say this table will be between row 1 to row10

Excelchat Expert
19/07/2018 - 03:15

the next table you get it you can select row 12 and it will put the second table from row 12

Excelchat Expert
19/07/2018 - 03:16

now, if there is a fixed logic like seperate all the elements with spaces first and separate numbers everytime there is a dit, you can import everything into one column and have formulas to split into various columns from here

Excelchat Expert
19/07/2018 - 03:17

that was the reason I asked how you would like to see the example data, if it were to be split manually

User
19/07/2018 - 03:17

Not sure what you mean

User
19/07/2018 - 03:17

Yes, it is split manually

User
19/07/2018 - 03:17

But after I import the text and split it there.. I would ideally like to copy and paste future texts and it automatically parse

Excelchat Expert
19/07/2018 - 03:17

from the string that you provided, does the ma,nual split reflect what you exêcted?

Excelchat Expert
19/07/2018 - 03:19

unfortunately any new sets will have to be imported, everytime there is a change. I am afraid without VBA, it is not possible to automate import

Excelchat Expert
19/07/2018 - 03:19

once imported, splits can be automated based on logics and formulas

User
19/07/2018 - 03:20

That is what I wanted the set to look like in columns

Excelchat Expert
19/07/2018 - 03:20

that i can demonstrate

User
19/07/2018 - 03:20

but I would like to copy paste it in a cell how it originally came... then automatically converts to that second table I created

Excelchat Expert
19/07/2018 - 03:22

let me demonstrate that

Excelchat Expert
19/07/2018 - 03:24

lets say I want to split the text in A4 nased on spaces

Excelchat Expert
19/07/2018 - 03:24

and then I will insert formulas and extend it to 10 lines

User
19/07/2018 - 03:24

Right

Excelchat Expert
19/07/2018 - 03:25

so that everytime a new text is inserted in column A it splits in the same way

User
19/07/2018 - 03:25

exactly

Excelchat Expert
19/07/2018 - 03:25

is this right direction?

Excelchat Expert
19/07/2018 - 03:25

perfect

User
19/07/2018 - 03:25

and that new text will come in the format of what is in A4

Excelchat Expert
19/07/2018 - 03:25

yes

Excelchat Expert
19/07/2018 - 03:25

so we are on same page unitl here

Excelchat Expert
19/07/2018 - 03:25

let me put those formulas

User
19/07/2018 - 03:25

Right

Excelchat Expert
19/07/2018 - 03:26

i will use mid

Excelchat Expert
19/07/2018 - 03:27

first I will use find and subsitute to find space in the text

Excelchat Expert
19/07/2018 - 03:36

there seems to be something wrong with find and substitute

Excelchat Expert
19/07/2018 - 03:36

let me quickly examine it

Excelchat Expert
19/07/2018 - 03:38

May I request on more extension of chat? although we will be done soon

Excelchat Expert
19/07/2018 - 03:52

So here you are

Excelchat Expert
19/07/2018 - 03:52

with the split formula

Excelchat Expert
19/07/2018 - 03:53

please try and insert the same string in A5

Excelchat Expert
19/07/2018 - 03:53

you will see that it it will split in the same way

Excelchat Expert
19/07/2018 - 03:56

I have added first, second, third occurance number of space to help and understand my formula better

Excelchat Expert
19/07/2018 - 03:56

I have added colors and header to explain what is what

Excelchat Expert
19/07/2018 - 03:56

I hope I answered your question satisfactorily.

Excelchat Expert
19/07/2018 - 03:56

Have a fantastic day :)

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user
information.*