Re: Returning Partial Strings

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Thu, 31 Jan 2008 14:44:02 -0800, ScottM <ScottM@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

I'm trying to separate a word/string of characters in a text field from the
rest of the field, but the strings are of varying lengths. Is there a way to
extract this? For example, my field contains the following values:

RAST 2006-1 M5
CWALT 2006-AR1 A

How can I extract the "RAST" and the "CWALT" from each?
How can I extract the "2006-1" and the "2006-AR1" from each?

Thanks in advance!

You can use the builtin string handling functions Left(), Mid(), and InStr().
To get the leftmost bit (before the first space) use

Left([fieldname], InStr([fieldname], " ") - 1)

The second bit is a bit more complex since you need to find both delimiting
blanks:

Mid([fieldname], InStr([fieldname], " ") + 1, Instr(Instr([fieldname], " ") +
1, [fieldname], " ") - InStr([fieldname], " ") - 1)

John W. Vinson [MVP]
.

Relevant Pages

  • Re: Extracting time from strings
    ... to hurt is in trying to determine the shift times in 24 hr format. ... Some rosters are 14 day, others are 28 although all will start on ... extract the employees name, position and section from the first columns. ... strings to extract the times. ...
    (microsoft.public.excel.programming)
  • Re: Extract text plus "n" characters or date on partial match
    ... Maybe starting from the same code I need to extract dates, first, ... extract the "rmd" strings. ... To extract the rmd strings as you write above, ... Dim i As Long ...
    (microsoft.public.excel.programming)
  • Re: Extract specific string
    ... Rick (MVP - Excel) ... Where do you want the extracted strings to go... ... > The string start always with "S" and finished with 8 digits and a VB ... Do the strings you are trying to extract ...
    (microsoft.public.excel.programming)
  • Re: Returning Partial Strings
    ... but the strings are of varying lengths. ... extract this? ... RAST 2006-1 M5 ...
    (microsoft.public.access.queries)
  • Re: Query on pattern matching
    ... I need to extract few strings from one file and paste it to another file. ... Since i am using spaces as delimiters here, i am facing problems. ...
    (perl.beginners)