change 1 field into 2

S

sternface

cannot find where to post a question on this site. I know it must be me.
and the question is how to separate 1 field into two fields in excel;
345 Main street is Field 1; I would like to separate it into two
Fields:
Field 1 345
Field 2 Main street

And also maybe how to do it in access?

Thanks
 
D

DL

In Access you use vba to read the field1, strip the number out, and append
it to a second field.
See vba help for the syntax.
I would imagine you do similar in Excel.
It would however be somewhat unusual to need the address number in a
seperate field.
 
B

Bob I

Parse it at the first space character. Using FIND, LEFT, LEN and RIGHT
functions.
 
S

Stewart

In Excel, use:-

Data
Text to Columns
then use space delimited and it will split 123 abc into 2 columns.

Stewart.
 
B

Bob I

Good idea, except "345 Main street" will make 3 columns, and "345 Main
street south" will be 4!
 
Top