I have a column that is a lenthy sentence downloaded from software. In each unique sentence there is an account number. Each account number starts with the same 3 digits and has a hyphen.

Is there any way to pull just this from the sentence into a column? Or, pull each thing out but seperate the account number?

Right now the best I can come up with is to put each word in a column that's seperated with a space... hoping for something better than this.

2/17/2011 3:21:00 PM

If you have to do this in excel then it would be better to use a regular expression to snag it. If it doesn't have to be excel, then it would be best to use perl to use a regular expression to snag it.

2/17/2011 5:33:47 PM

=LEFT(RIGHT(A1,LEN(A1)-SEARCH("@@@",A1)+1),#)

where
A1 = cell which has the sentence
@@@ = the first three digits of the account number
# = the number of characters in the account number (including the hyphen)

this worked on a quick test case in excel 2010. you may have to fiddle with it to get it to work for you.

OR

=LEFT(RIGHT(A1,LEN(A1)-SEARCH("-",A1)+4),#)

this works for any account number assuming that the format is ###-#####... and the account number is a standard length each time. it also assumes there are no other hyphens in the sentence.

again:

A1 = cell which has the sentence
# = the number of characters in the account number (including the hyphen)


[Edited on February 18, 2011 at 1:47 AM. Reason : .]

2/18/2011 1:42:23 AM