+ Reply to Thread
Results 1 to 8 of 8

Selective Comma removal to enable delimiting

  1. #1
    Registered User
    Join Date
    08-07-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Selective Comma removal to enable delimiting

    Hi,

    Hopefully somebody can help if this can be done at all.

    I'm currently trying to complete a mailmerge with approx 6500 records but the addresses in approx half of the cells are formatted incorrectly (an unavoidable issue with data that's extracted from our own bespoke software) and inserts a comma after the house number of the street name. Example as below (false data used for confidentiality purposes) :

    1 Mickey Mouse Street, Disneyland, Florida, FL1 2MM
    3, Donald Duck Street, Disney World, Paris, PA1 2DD
    5, Goofy Plaza, Goof Town, Goofsville, GG3 8GG
    7 Minnie Mouse Road, Minnie Town, MM8 7MM
    9 Yosemite Lane, Yosemite, YY5 7YY

    What I'm looking to do is automatically remove the commas after the numbers in lines 2 and 3 in this example (eg so 3, Donald Duck Street becomes 3 Donald Duck Street in the same formate as the other 3 lines) thus enabling me to delimit by comma and mailmerge data into an MSWord doc.

    The above examples only list 5 addresses but in reality there are 6500 so a manual trawl and correction is really out of the question.

    If this can even be done could somebody advise how?

    Thanks in advance and hope this request makes sense.

    Andrew
    Last edited by Andrew M SB; 08-07-2013 at 08:43 AM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Selective Comma removal to enable delimiting

    Hello Andrew & Welcome to the Forum,

    Strictly based off of your sample data, try...

    =IF(FIND(",",A1)=2,SUBSTITUTE(A1,",","",1),A1)
    HTH
    Regards, Jeff

  3. #3
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Selective Comma removal to enable delimiting

    Presumably not all house numbers will be single digit. How about modifying your code so it searches in the first (say) 5 characters for a comma?
    My approach to providing help is to help you to help yourself. So my answers won't always solve your problem, but hopefully you can learn enough to solve the problem yourself and be more self sufficient for the experience.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Selective Comma removal to enable delimiting

    Thanks Mallycat...this is why you should not operate Excel on three hours of sleep.

    =IF(FIND(",",A1)<SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))),SUBSTITUTE(A1,",","",1),A1)

  5. #5
    Registered User
    Join Date
    08-07-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Selective Comma removal to enable delimiting

    Thanks guys,

    I will give this a try in a couple of hours.

    Your fast response has been much appreciated.

  6. #6
    Registered User
    Join Date
    08-07-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Selective Comma removal to enable delimiting

    It works!

    Thank you SO much!

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Selective Comma removal to enable delimiting

    You're welcome…glad you have a workable solution and thanks for the feedback...

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  8. #8
    Registered User
    Join Date
    08-15-2013
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: Selective Comma removal to enable delimiting

    Hello!
    I have the same problem! And this formula seems to be my solution, but I'm new to Excel (used it like 5 times in my life!) and I don't know how to use this function! My data is in B, with a header in B1 (in case I would have to change the formula)

    Could you explain how to use it, like I'm a 5 years old?

    Thanks in advance!

    PS: If my post does not comply with Rule 2 of the Forum, I'm sorry. But since it was only about "how" to apply the function, I thought it wouldn't matter... If it does, I'm sorry! :S
    Last edited by DaSniper; 08-15-2013 at 12:33 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 12-11-2012, 05:04 AM
  2. [SOLVED] Delimiting with a formula
    By finwiz in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-28-2012, 05:51 PM
  3. Tab Delimiting with even lines
    By Nophy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-14-2009, 07:43 PM
  4. Delimiting without truncating
    By sirlink in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-22-2009, 01:40 PM
  5. delimiting question
    By santoallwaysfun in forum Excel General
    Replies: 1
    Last Post: 06-27-2008, 06:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1