Solved

# formula or macro to split txt

Posted on 2014-02-21
152 Views
Hi experts excel 2007

I have in col o3 the following name and address which I want to split in individual cells u3 on wards
O3= mr psrm simn john 37 devon close fleetwoid fy7 7ea

Whats the best way to achieve this
0
Question by:route217
• 2
• 2

LVL 8

Expert Comment

how do u want to split text? one word per cell?
0

Author Comment

Apologies thats correct.
0

LVL 13

Expert Comment

=+LEFT(O3,FIND(" ",O3)-1)   Will give you the first word
=+MID(O3,FIND(" ",O3)+1,LEN(O3))    Will give you the remaining text

Keep repeating the formulae until you have separated each word
0

LVL 13

Assisted Solution

akb earned 250 total points
Came up with better formulae:

=TRIM(MID(SUBSTITUTE(O3," ",REPT(" ",30)),30*(XXX-COLUMN(O3))+1,30))

Substitue XXX for whichever word you are after

eg. if you want the third word use:
=TRIM(MID(SUBSTITUTE(O3," ",REPT(" ",30)),30*(3-COLUMN(O3))+1,30))
0

LVL 8

Accepted Solution

itjockey earned 250 total points

Thanks
0

## Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…