Expert Solution - Excel COLUMN Problems

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

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

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