Insert comma in (some) address lines

[oldchippy]

Hi,


I'm stumped with this one, I need a formula (not VBA) that will insert a comma, this is a sample list before and after.


14 WOLVERTON CLOSE

UNIT 31 HENLEY COURT

PERSHORE DEPOT

UNITS 17-23A KERNAN DRIVE

25A OAKCROFT ROAD

53-54 ALLCOCK ST

MIDDLETON TECHNICAL CENTRE

73/74 BUSINESS PARK

CHURCHILL HOUSE

UNIT 1C THE CASTLE


And after


14, WOLVERTON CLOSE

UNIT 31, HENLEY COURT

PERSHORE DEPOT

UNITS 17-23A, KERNAN DRIVE

25A, OAKCROFT ROAD

53-54, ALLCOCK ST

MIDDLETON TECHNICAL CENTRE

73/74, BUSINESS PARK

CHURCHILL HOUSE

UNIT 1C, THE CASTLE


As you can see some rows do not have any numbers in them.


Thanks for any replies

 

[Luke M]

I think this is easier to do with a helper column. First, to find the first space after a number, if it exists, use this array formula (I'll assume this is in B2):

=FIND(" ",A2,MAX(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("a1:a" & LEN(A2))),1)),ROW(INDIRECT("a1:a"&LEN(A2))))))


Next formula to build desired output:

=IF(ISNUMBER(B2),LEFT(A2,B2-1)&","&MID(A2,B2,LEN(A2)),A2)

 

[oldchippy]

Hi Luke,


Well that was quick, thanks so much with that. Would you mind explaining how that works exactly???


Thanks again for your help.

 

[Luke M]

Sure. The first formula needs to breakdown the entire string into the different characters, and then analyze each one. Starting at inside function, working out...

INDIRECT is used to get the range address for a group of cells equal to length of word. ROW then converts that string into the numbers 1 - n (n = length of word). The MID function then uses those numbers to seperate out each character from the string. The "--" is to convert any numbers stored as text (MID outputs everything as text) to an actual number.

The IF & ISNUMBER functions look at each character from the MID function, determines if it's a number, then gives me it's location (using the ROW/INDIRECT functions again). MAX gives me the spot of last number in your string.

After that, the FIND function simply looks for first space after that number found.

If no number is in string, the MAX function will err out, resulting in overall error.


The 2nd formula checks to see if a number was found (aka, no error), and then splits the word in correct spot and adds the comma.


The trick was figuring out how to get the 1st formula to look at each individual character within the string

 

[oldchippy]

Luke,


Thanks for explaining it, I'll now try and get my head around it all.


If you were close enough I'd buy you a pint!