Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 14th 05, 10:23 PM
Rbp9ad
 
Posts: n/a
Default Find first numeric value in text string

I need a formula that returns the account number and only the account number
from a text string that looks like this.
BAN 123456 10/1/05-10/31/05
or
ACCT 78-910 9/25/05 - 10/26/05
or
111213 1415 9/7/05 - 10/6/05
the formula that I have so far is this.
=TRIM(MID(TRIM(RRLookup(A9,11)),FIND("9",TRIM(RRLo okup(A9,11))),FIND("/",RRLookup(A9,11))-2))
RRLookup is a custom function that returns text strings like the examples.
This formula is for an account number that begins with 9.
I want it to be more general and just start at the first numeric character.
Is there a way to do this?



  #2   Report Post  
Old October 14th 05, 10:46 PM
Biff
 
Posts: n/a
Default

Hi!

BAN 123456 10/1/05-10/31/05
ACCT 78-910 9/25/05 - 10/26/05
111213 1415 9/7/05 - 10/6/05


Where does the account number end in the 3rd example?

If all strings were like the first 2 this would be relatively easy. If the
account number always ends before the 2nd space this should not be too
difficult. Find the 1st digit, find the 2nd space, return everything
between. But, if some stings might look like this:

ACCT 111213 1415 9/7/05 - 10/6/05
BAN 123456 10/1/05-10/31/05
ACCT 78-910 9/25/05 - 10/26/05
111213 1415 9/7/05 - 10/6/05
XX 111213 1415 04 9/7/05 - 10/6/05

There may be too many variables to consider.

Biff

"Rbp9ad" wrote in message
...
I need a formula that returns the account number and only the account
number from a text string that looks like this.
BAN 123456 10/1/05-10/31/05
or
ACCT 78-910 9/25/05 - 10/26/05
or
111213 1415 9/7/05 - 10/6/05
the formula that I have so far is this.
=TRIM(MID(TRIM(RRLookup(A9,11)),FIND("9",TRIM(RRLo okup(A9,11))),FIND("/",RRLookup(A9,11))-2))
RRLookup is a custom function that returns text strings like the examples.
This formula is for an account number that begins with 9.
I want it to be more general and just start at the first numeric
character. Is there a way to do this?




  #3   Report Post  
Old October 14th 05, 11:13 PM
Rbp9ad
 
Posts: n/a
Default

The account numbers are quite variable because they are from different
vendors, but we have many account numbers with each vendor. The account
number will always start at the first numeric value and end one or two
spaces before the first / for the date. I want to drop the ACCT or BAN or
Bill No. that appears in front of these numbers and the dates that are after
them. If this can not be done then could you explain to me how to return the
position in a string of the nth occurence of a character so I can write a
formula for each vendor (Account numbers from the same vendor always have
the same format).
"Biff" wrote in message
...
Hi!

BAN 123456 10/1/05-10/31/05
ACCT 78-910 9/25/05 - 10/26/05
111213 1415 9/7/05 - 10/6/05


Where does the account number end in the 3rd example?

If all strings were like the first 2 this would be relatively easy. If the
account number always ends before the 2nd space this should not be too
difficult. Find the 1st digit, find the 2nd space, return everything
between. But, if some stings might look like this:

ACCT 111213 1415 9/7/05 - 10/6/05
BAN 123456 10/1/05-10/31/05
ACCT 78-910 9/25/05 - 10/26/05
111213 1415 9/7/05 - 10/6/05
XX 111213 1415 04 9/7/05 - 10/6/05

There may be too many variables to consider.

Biff

"Rbp9ad" wrote in message
...
I need a formula that returns the account number and only the account
number from a text string that looks like this.
BAN 123456 10/1/05-10/31/05
or
ACCT 78-910 9/25/05 - 10/26/05
or
111213 1415 9/7/05 - 10/6/05
the formula that I have so far is this.
=TRIM(MID(TRIM(RRLookup(A9,11)),FIND("9",TRIM(RRLo okup(A9,11))),FIND("/",RRLookup(A9,11))-2))
RRLookup is a custom function that returns text strings like the
examples. This formula is for an account number that begins with 9.
I want it to be more general and just start at the first numeric
character. Is there a way to do this?






  #4   Report Post  
Old October 14th 05, 11:37 PM
Domenic
 
Posts: n/a
Default

Maybe...

=TRIM(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789")),(FIND("/"
,A1)-3)-(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") )-1)))

Hope this helps!

In article ,
"Rbp9ad" wrote:

I need a formula that returns the account number and only the account number
from a text string that looks like this.
BAN 123456 10/1/05-10/31/05
or
ACCT 78-910 9/25/05 - 10/26/05
or
111213 1415 9/7/05 - 10/6/05
the formula that I have so far is this.
=TRIM(MID(TRIM(RRLookup(A9,11)),FIND("9",TRIM(RRLo okup(A9,11))),FIND("/",RRLoo
kup(A9,11))-2))
RRLookup is a custom function that returns text strings like the examples.
This formula is for an account number that begins with 9.
I want it to be more general and just start at the first numeric character.
Is there a way to do this?

  #5   Report Post  
Old October 15th 05, 02:01 AM
Biff
 
Posts: n/a
Default

Try Domenic's suggestion. It looks like it should work.

could you explain to me how to return the position in a string of the nth
occurence of a character


Assuming you know how many instances of the character there a

Using this string as an example:

ACCT 111213 1415 9/7/05 - 10/6/05

=FIND("~",SUBSTITUTE(A1,"/","~",4))

Returns the position of the 4th "/".

Basically, what that does is substitutes the nth instance of "/" with "~"
and uses the "~" as a "unique marker" for the Find function. I used the
tilde character as it's a character that is not likely to appear in the
string.

To find out how many instances of any character there are in a string:

=LEN(A1)-LEN(SUBSTITUTE(A1,"character_to_count",""))

Biff

"Rbp9ad" wrote in message
...
The account numbers are quite variable because they are from different
vendors, but we have many account numbers with each vendor. The account
number will always start at the first numeric value and end one or two
spaces before the first / for the date. I want to drop the ACCT or BAN or
Bill No. that appears in front of these numbers and the dates that are
after them. If this can not be done then could you explain to me how to
return the position in a string of the nth occurence of a character so I
can write a formula for each vendor (Account numbers from the same vendor
always have the same format).
"Biff" wrote in message
...
Hi!

BAN 123456 10/1/05-10/31/05
ACCT 78-910 9/25/05 - 10/26/05
111213 1415 9/7/05 - 10/6/05


Where does the account number end in the 3rd example?

If all strings were like the first 2 this would be relatively easy. If
the account number always ends before the 2nd space this should not be
too difficult. Find the 1st digit, find the 2nd space, return everything
between. But, if some stings might look like this:

ACCT 111213 1415 9/7/05 - 10/6/05
BAN 123456 10/1/05-10/31/05
ACCT 78-910 9/25/05 - 10/26/05
111213 1415 9/7/05 - 10/6/05
XX 111213 1415 04 9/7/05 - 10/6/05

There may be too many variables to consider.

Biff

"Rbp9ad" wrote in message
...
I need a formula that returns the account number and only the account
number from a text string that looks like this.
BAN 123456 10/1/05-10/31/05
or
ACCT 78-910 9/25/05 - 10/26/05
or
111213 1415 9/7/05 - 10/6/05
the formula that I have so far is this.
=TRIM(MID(TRIM(RRLookup(A9,11)),FIND("9",TRIM(RRLo okup(A9,11))),FIND("/",RRLookup(A9,11))-2))
RRLookup is a custom function that returns text strings like the
examples. This formula is for an account number that begins with 9.
I want it to be more general and just start at the first numeric
character. Is there a way to do this?










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I convert text string into a cell reference Dave Davis Excel Discussion (Misc queries) 3 June 10th 08 09:38 PM
find position of a number in a string fullers80 Excel Worksheet Functions 1 September 6th 05 03:47 PM
How to import a text file to Excel treating all input content are in string. Chittu Excel Discussion (Misc queries) 1 July 22nd 05 06:37 AM
Finding Specific Text in a Text String Peter Gundrum Excel Worksheet Functions 9 April 10th 05 07:21 PM
Formating a text string? METCO1 Excel Discussion (Misc queries) 2 November 30th 04 06:31 PM


All times are GMT +1. The time now is 02:07 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017