find first number in text string

[Madapple]

so here is my problem

i have a list of addresses. the entire address is in one field.

ie: John Doe 1234 street city state zip

i need to break it out to atleast 2 fields

ie: first last

1234 street city state zip

in all records the address starts with a number.

can i create a calculation that will find the first number in the field and then populate 2 new fields with the now parsed information.

i have been able to create a formula in excel that gives me the first number - but so far have been unable to find someway to split the name and address.

ideas?

thanks

B

[comment]

Try:

Let ( [

num = Filter ( text ; "01234567889" ) ;

firstNum = Left ( num ; 1 ) ;

pos = Position ( text ; firstNum ; 1 ; 1 )

] ;

Trim ( Left ( text ; pos - 1 ) )

)

for the name part. To get the address part, change the last line to:

Right ( text ; Length ( text ) - pos + 1 )

[TheTominator]

Here's one way to do it in a calc.

This gives the first and lastname part.

Alter the formula to use "address" as its result to obtain everything on the rest of the line starting with a number.

Let(

[

originalText = "John Smith 123 Sample Avenue Anytown USA";

addressList1 = Substitute( originalText;

["0"; "¶"];

["1"; "¶"];

["2"; "¶"];

["3"; "¶"];

["4"; "¶"];

["5"; "¶"];

["6"; "¶"];

["7"; "¶"];

["8"; "¶"];

["9"; "¶"]

);

firstlast = Trim(GetValue(addressList1; 1));

address = Trim(Right(originalText; Length(originalText) - Length(firstlast)))

];

firstlast

)

[Madapple]

PERFECT!!!