Splitting data from one cell to 2 cells by formula

**Derek B** · 01-08-2015, 05:23 AM

Hi guys,

I am looking to split data that comes into me through an adobe form questionnaire. When i paste the data into excel it all goes into one column and single cells. Rather than each time use the text to columns each time i am looking to paste in the data and have a formula in the next two cells that split the data. There is a common colon ':' where the data and answer is split on each question and answer. for example

Title: mr
Full Name: derek b
Telephone: 555
Full Address: 1234

With each new form i just want to paste it into a single column and the data will be automatically split into two additional columns.
I hope that I am explaining this right.

Any help would be appreciated,
Thanks in advance,

Derek.

**Glenn Kennedy** · 01-08-2015, 05:32 AM

You can use Data/Data Tools/Text to Columns/other/:

but this will leave a leading space in front of the text, which might be a nuisance later one... You can get rid of it using a formula =TRIM(cell ref).

**tchock** · 01-08-2015, 05:33 AM

Hi Derek,

try this:

col A
Title: mr

col B
=LEFT(A1,FIND(":",A1,1))

col C
=RIGHT(A1,LEN(A1)-FIND(":",A1,1)-1)
drop the '-1' if you want to keep the space or if you don't always have a space after ':'

**davesexcel** · 01-08-2015, 05:35 AM

Have you tried text to Columns?
Check this out
http://blog.contextures.com/archives...ames-in-excel/
Use colon as the delimiter in your case.

**Derek B** · 01-08-2015, 05:37 AM

tchock

you are a legend. thanks very much and for your quick reply. much appreciated.
Exactly what i needed

Derek.

**tchock** · 01-08-2015, 05:41 AM

I'm glad I could help you!

**Derek B** · 01-08-2015, 05:42 AM

I did thanks Dave but i was looking for something where i didn't have to run the wizard each time.

tchock formulas above work great and just what i needed. thanks again.