Extracting Digits in a Series of Numbers 2

[Texanite]

I have a column B with several rows of numbers that look like this 2-59020-3. I need to isolate the middle number and put it in column A. The problem is that the 1st number could be anywhere from 1 to 5 characters long. The last number is always 1 digit. Is there ia way to do this?

Thanks,
Texantie

 

[SkipVought]

Hi,

Assuming that your values are in COL A...

=MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)-2)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

Regardless of length of any number in the string, as long as you always have two hyphens straddling the number you want:-

For a textual return of the number:-

=MID(A1,FIND("-",A1)+1,FIND("%",SUBSTITUTE(A1,"-","%",2))-(FIND("-",A1)+1))

For a numeric return:-

=--MID(A1,FIND("-",A1)+1,FIND("%",SUBSTITUTE(A1,"-","%",2))-(FIND("-",A1)+1))

If the number in the middle is ALWAYS 5 digits long, then:-

For text:-

=MID(A1,FIND("-",A1)+1,5)

For numeric:-

=--MID(A1,FIND("-",A1)+1,5)

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[KenWright]

Regardless of length of any number in the string, as long as you always have two hyphens straddling the number you want, with your data in Col B starting B1, in A1 put the following and copy down:-

For a textual return of the number:-

=MID(B1,FIND("-",B1)+1,FIND("%",SUBSTITUTE(B1,"-","%",2))-(FIND("-",B1)+1))

For a numeric return:-

=--MID(B1,FIND("-",B1)+1,FIND("%",SUBSTITUTE(B1,"-","%",2))-(FIND("-",B1)+1))

If the number in the middle is ALWAYS 5 digits long, then:-

For text:-

=MID(B1,FIND("-",B1)+1,5)

For numeric:-

=--MID(B1,FIND("-",B1)+1,5)

The other way of course, is just to use Data / text to columns / Delimited - Check 'Other' and put a '-' in the other field

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[KenWright]

Oops - Didn't think the first went through :-(

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[WildHare]

Not sure if this is a Windows or an Excel/Access question....

Basically, you need to parse out your chunks of numbers,by looking for the "-" (dash) characters. For example, using Access VBA, to find the "first" set of 1-n numbers, you'd use

Left([number-set], 1, Instr([number-set], "-&quot-1)

giving you the 'leftmost' set of numbers, from position 1 through the position of the dash less one

1-33929-0 yields "1"
239-0394-049 yields "239"
33829-392-0393 yields "33829"

To find the MIDDLE set, what I'd probably do here is chunk out the data itself by chopping off the "first set" of numbers...

To break it down:

Given a string like "123-4079-32", call it BigString

The first dash is Instr([BigString],"-&quot, or 4

Now, let's reset BIGSTRING to BIGSTRING starting at position 4+1
BigString = Mid(BigString, Instr([bigString],"-&quot+1)

So now BigString is "4079-32"

Find the first dash, as outlined above. It should be a position 5, right?

The MIDDLE set of numbers is the characters from 1 through where the first dash is above, less 1. Exactly like above...

Get it? This should be enough to get you started.

Jim




If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[WildHare]

See what happens when you start a reply at about 2 pm and finally get around to posting it at 4:45..

J

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[kraptaki]

If your number is in cell B1 (e.g. 12-152452-456) write this formula in cell a1 and copy it downwards:
=MID(B1,FIND("-",B1,1)+1,FIND("-",B1,1+FIND("-",B1,1))-FIND("-",B1,1)-1)

Hope this will help!