Solved

formula or macro to split txt

Posted on 2014-02-21
5
152 Views
Last Modified: 2014-02-21
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
Comment
Question by:route217
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:itjockey
Comment Utility
how do u want to split text? one word per cell?
0
 

Author Comment

by:route217
Comment Utility
Apologies thats correct.
0
 
LVL 13

Expert Comment

by:akb
Comment Utility
=+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

by:akb
akb earned 250 total points
Comment Utility
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

by:
itjockey earned 250 total points
Comment Utility
ABC
Thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now