macro

V

vince cash

i need a macro to find parts of an address and move to another colmun. like

213 smith rd apt 2
that is in column e and move or copy just the apt 2 in column f and then
delete it from column e.
i am a little lost on what i need to do.
 
D

dolivastro

You need to tell us more. Are you trying to cut/copy all words that
appear after the word "rd", or are you trying to cut/copy all words
that appear on and after "apt"? There must be some lexical clue in the
cell.

To get you started, I'll assume the lexical clue is the word "apt".
Then use this:

------------------------------------
dim idx as long
dim Text as string
dim Text_1 as string
dim Text_2 as string
dim r as long
dim s as excel.worksheet


const p_maxRow as long = ???

set s = ActiveSheet

for r = 1 to p_maxRow
Text = s.Cells (r, "E").Value
idx = InStr (1, Text, "apt", vbTextCompare)
if (idx > 0) then
Text_1 = mid (Text, 1, idx-1)
Text_2 = mid (Text, idx)
s.Cells (r, "E").Value = Text_1
s.Cells (r, "F").Value = Text_2
endif
next r
 
D

Dinesh

Its in fact very simple if you use formulae in place of macros. Some of the
formulae are left, right, mid, find, search etc. I used it and its very
useful for splitting words and sentences, in fact you can create very complex
functions using combinations of these.

Dinesh
 
D

Dinesh

Hi, In fact I belive its better to use formulae instead of macros. I used the
following functions for splitting words and sentences. Mid, left, right,
search, find and so on. Try it out its simple and you can create very complex
functions with combination of these.

Dinesh
 
Top