Results 1 to 5 of 5

Insert comma in string

This is a discussion on Insert comma in string within the Excel Questions forums, part of the Question Forums category; How do you insert a comma into a string in the position before the first number in the string. The ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    97

    Default

    How do you insert a comma into a string in the position before the first number in the string. The string contains both numbers and letters.I have columns with names and addresses that have no space between the end of name and beginning of the address. The most likely occurance are #'s after the name, yes I know PO BOX and RR etc will mess it up, but have to start somewhere, Then I want to insert the comma and then use text to columns to parse. If there is a better way let me know. Thanks!!!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,910

    Default



    Shouldn't you give some examples?

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Posts
    97

    Default

    Yes I should. My Oversite Thank You!!
    STORAGE USA2135 COLUMBIANA RD
    A SAFE MINI STORAGE2650 E SOUTH BLVD
    AAA MINI STORAGE441 HACKEL DR
    ACE MINI SELF-STORAGE3620 DAY ST
    ADVANCE MOVING & STORAGE254 HUNTER ST
    ADVANCED SELF STORAGE6010 MONTICELLO DR
    ALABAMA MINI STORAGE561 OLIVER RD
    AMERICAN MINI STORAGE2931 DAY ST
    A LOW COSTSELF STORAGE 7211 ARLINGTON AVE
    PUBLIC STORAGE 6379 MISSION BLVD

    Notice some have a space others don't. I can eliminate extra spaces later but, I need a comma , it will be easier to parse to separate into column. As far as PO and RR I ca use find and replace to coorect and inser comma.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,910

    Default

    On 2002-06-29 06:31, tagger wrote:
    Yes I should. My Oversite Thank You!!
    STORAGE USA2135 COLUMBIANA RD
    A SAFE MINI STORAGE2650 E SOUTH BLVD
    AAA MINI STORAGE441 HACKEL DR
    ACE MINI SELF-STORAGE3620 DAY ST
    ADVANCE MOVING & STORAGE254 HUNTER ST
    ADVANCED SELF STORAGE6010 MONTICELLO DR
    ALABAMA MINI STORAGE561 OLIVER RD
    AMERICAN MINI STORAGE2931 DAY ST
    A LOW COSTSELF STORAGE 7211 ARLINGTON AVE
    PUBLIC STORAGE 6379 MISSION BLVD

    Notice some have a space others don't. I can eliminate extra spaces later but, I need a comma , it will be easier to parse to separate into column. As far as PO and RR I ca use find and replace to coorect and inser comma.
    Let A1 house the first entry that needs a comma at the appropriate position.

    In B1 array-enter and copy down as far as needed.

    =MATCH(1,ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0)+0,0)

    Note. In order to array-enter a formula you need to hit control+shift+enter at the same time, not just enter.

    In C1 enter and copy down:

    =REPLACE(A1,B1,1,","&MID(A1,B1,1))

    The Text to Columns bit I leave to you.

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Posts
    97

    Default

    Thank Alot!!! It worked like a Charm!!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com