how do i add commas in a single cell?

[Footlight]

okay, so i have a single line of imported addresses where the entire address
is in one cell. I don't want to move cities, etc. to another cell; I just
want to add comma's in the appropriate places. Is there a way to do that?

i.e. :

Cell A1
123 Anywhere St Somewhere in US 12345

Becomes:

Cell A1
123 Anywhere St, Somewhere in US, 12345

 

[Rick Rothstein]

Basically, no... since street names and cities (and states for that matter)
can contain multiple (space delimited) words, there is no way to tell where
the address part ends and the city part begins. You might be be able to
construct a list of all the possible roadway designations (Street, Lane,
Road, Way, Court, etc.) and search for them; but, if you do this, you would
probably have to do this as abbreviations since I am aware of at least one
roadway in Philadelphia, Pennsylvania that is called 'Street Road', so you
would need to be able to distinguish such street name and roadway
designation combinations. And this would take VBA code to accomplish. Also,
you would have to make sure you accounted for all the possible roadway
designations possible (here I am thinking of St and Str both being
abbreviations for Street as an example).

 

[Spiky]

Imported from where? Can you put the commas in before it gets to
Excel? Maybe save/import in a different format instead that Excel can
use more easily?

Or, if you can separate them properly to other cells, it will be
easier to put them back into one cell with commas (using Concatenate)
than the VBA method mentioned by Rick.