+ Reply to Thread
Results 1 to 3 of 3

search for specific characters in text string and separate them out

  1. #1
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    search for specific characters in text string and separate them out

    I have a column which contains text and somewhere within the text there may or may not be characters in the format n/nnn where n is a number e.g. "1/021" or "4/054".

    What I need to do is find anything in the string matching this format and pull it out of the original string leaving the string without the n/nnn and the n/nnn on its own in another column.

    I tried various left, right, len etc but couldnt get the desired result.

    something like
    existing single column:
    mess room 5/043
    3/032 toilet
    passage 3/022 booking hall

    would end up in 2 columns as (numbers in 1 colunm and rest of text in another):
    5/043 mess room
    3/032 toilet
    3/022 passage booking hall

    If it cant be done with formulas I may be able to run a macro instead.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    If your original string is in A1, try:

    In B1: =MID(A1,FIND("/",A1)-1,5)

    In C1: =TRIM(SUBSTITUTE(A1,B1,""))

    both copied down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154
    thanks that worked

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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