Getting Specified Data Out Of A Long Text String
What if you only wanted to extract portions of data out of a long text string? For example, how can you extract the string 0001 from the entry 111111111100010000000000?
- Use the Mid() function, but there are two conditions:
* The Mid() function returns a string.
* The text entries must be consistent in the number of characters and the position of the extracted string.
- The Mid() function parses characters from a string (or a value) using the form:
Mid(STRING, FIRSTPOSITION, NUMBEROFCHARACTERS)
where STRING is the text you're parsing, FIRSTPOSITION is the position of the first character you want to extract, and NUMBEROFCHARACTERS is the total number of characters you want to extract.
- =MID(A1,11,4) Assuming STRING is a cell address that contains the value 111111111100010000000000, this function will parse the characters at positions 11, 12, 13, and 14, which returns 0001.
This function will work with any text string and parse from a starting position through the number of characters or spaces required.
Tip initially comes from Susan Sales Harkins, a private consultant specializing in Access and VBA development. She currently has two technical books on the shelves: "Using Microsoft Access 97" and "Using Microsoft Access 2000." Both are QUE publications.
Just click on the button for the Tiproom's Home Page.
Date of last revision: 21 August 2001.