+ Reply to Thread
Results 1 to 5 of 5

Find the 1st TEXT CHARACTER in a text string and add a X character right before it

  1. 04-05-2014, 11:12 AM #1
    dvpe
    dvpe is offline
    Registered User
    Join Date
    04-05-2014
    Location
    São Paulo, Brasil
    MS-Off Ver
    Excel 2007
    Posts
    12

    Find the 1st TEXT CHARACTER in a text string and add a X character right before it

    Hi folks, here is the deal...

    I got these columns...
    [A1] 01-Suck it Up.mp3
    [A2] 01 Suck it Up.mp3
    [A3] 1-Suck it Up.mp3
    [A4] 1 Suck it Up.mp3
    [A5] 1\01-Suck it Up.mp3

    So, I want to replace " " or "-" characters with ";" right before the first TEXT character found in the string.
    Then the string would end up in the column B like this...

    [B1] 01;Suck it Up.mp3
    [B2] 01;Suck it Up.mp3
    [B3] 1;Suck it Up.mp3
    [B4] 1;Suck it Up.mp3
    [B5] 1\01;Suck it Up.mp3
    Reply With QuoteRegister To Reply
  2. 04-05-2014, 11:24 AM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Find the 1st TEXT CHARACTER in a text string and add a X character right before it

    try this one

    =SUBSTITUTE(SUBSTITUTE(A1,"-"," ")," ",";",1)

    A
    B
    1
    		 01-Suck it Up.mp3 01;Suck it Up.mp3
    2
    		 01 Suck it Up.mp3 01;Suck it Up.mp3
    3
    		 1-Suck it Up.mp3 1;Suck it Up.mp3
    4
    		 1 Suck it Up.mp3 1;Suck it Up.mp3
    5
    		 1\01-Suck it Up.mp 1\01;Suck it Up.mp
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 04-05-2014, 11:27 AM #3
    wcymiss
    wcymiss is offline
    Registered User
    Join Date
    03-30-2014
    Location
    Suzhou,China
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Find the 1st TEXT CHARACTER in a text string and add a X character right before it

    Hi,plz try this:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Welcome to China,Welcome to Suzhou...And,我是来学英文的。
    Reply With QuoteRegister To Reply
  4. 04-05-2014, 11:30 AM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Find the 1st TEXT CHARACTER in a text string and add a X character right before it

    Quote Originally Posted by wcymiss View Post
    Hi,plz try this:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    You don't need CTRL+SHIFT+ENTER for this
    Reply With QuoteRegister To Reply
  5. 04-06-2014, 01:21 PM #5
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Find the 1st TEXT CHARACTER in a text string and add a X character right before it

    Thank you for the feedback!

    Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] find the character position in a string of the last occurrence of a nominated character
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2014, 11:52 PM
  2. Macro to find character and delete all text in cell after the character
    By SpencerRichman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2012, 06:08 PM
  3. Find last repeated character position from a text string
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-17-2011, 02:14 PM
  4. Text parsing, find string after : character
    By proepert in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-14-2010, 10:46 PM
  5. [SOLVED] Find last occurance of character in text string
    By JDay01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2006, 12:35 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1