EXCEL FEED: RE: [ExcelVBA] Separating House # and street name currently in one column

Sabtu, 28 Oktober 2017

RE: [ExcelVBA] Separating House # and street name currently in one column

Hi Mark

I assume that your list has a consistent format, that is "number" "space" "Street name which may include more spaces".

If so, I would use a text search functions called LEN(), FIND(), LEFT() and RIGHT().

First, get the LENgth of the string in a cell. You will need this later.

Next, FIND the position of the first SPACE, starting from the left-most character in the string.

Now, extract the LEFT hand side of the string up to the position of the SPACE, minus one character so the SPACE is not included.

Using the same logic, extract the RIGHT hand side of the text string, starting from the position of the SPACE character, for the length of LEN minus the position of the SPACE character. Add one character or subtract one character as appropriate to include all text but not the preceding space.

Regards

Paul

PS: If insist on using macros, you can record all this using "relative references", and ensure your last cursor moves are to the next starting pint a row below. I normally record withour relative references, and then analyse the code to turn it into a From To loop.

From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Friday, 27 October 2017 3:53 AM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Separating House # and street name currently in one column

I have a list of about 4000 addresses that currently have the house number and street name together in a column/cell. I need to separate them so that the house number is in one column and the street name is in the adjacent column.

I think I have seen this problem in topics several years ago, but cannot find it. Can anyone help out. I have tried recording Macros, but it keeps repeating the street name from the first record.

Mark

Austin, TX

__._,_.___

Posted by: Paul Vermeulen <paul.vermeulen@vulcantech.com.au>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
------------------------------------

Visit Your Group