# Excel - COLUMN Function Problem - Expert Solution

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
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
Excelchat Expert 19/07/2018 - 03:56
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.