Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Rich Hayes
 
Posts: n/a
Default How do I look up a number within a string of text

In column A I have 1000 rows of text.Within each line of text in this column
is a 5 digit number that is at different points within the text. Is there any
way I can extract the 5 digit number and have it in column B alongside the
text it came from?

e.g. some text is dfgjdfgdfgd14532sfddfgdfg
other text is dfgsdfgsdfgdfgdfgdfgdf14321
i.e totally random text but with a number in there.

If it was always at the end of the text i'd simply do a formula =RIGHT(A1,5)
However it's not that simple.

Any help is greatly appreciated

Regards

Rich
  #2   Report Post  
N Harkawat
 
Posts: n/a
Default

=--MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),SUMPRODU CT(--(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))
array entered (ctrl+shift+enter)

Provided all the numbers are clustered together and not interspersed between
text at different positions






"Rich Hayes" wrote in message
...
In column A I have 1000 rows of text.Within each line of text in this
column
is a 5 digit number that is at different points within the text. Is there
any
way I can extract the 5 digit number and have it in column B alongside the
text it came from?

e.g. some text is dfgjdfgdfgd14532sfddfgdfg
other text is dfgsdfgsdfgdfgdfgdfgdf14321
i.e totally random text but with a number in there.

If it was always at the end of the text i'd simply do a formula
=RIGHT(A1,5)
However it's not that simple.

Any help is greatly appreciated

Regards

Rich



  #3   Report Post  
Stefi
 
Posts: n/a
Default

That's great! But is not a simpler solution an UDF like this?

Public Function Numfind(istr As String) As String
Keres = ""
For i = 1 To Len(istr)
If IsNumeric(Mid(istr, i, 1)) Then
Numfind = Mid(istr, i, 5)
Exit Function
End If
Next i
End Function

Stefi


€˛N Harkawat€¯ ezt Ć*rta:

=--MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),SUMPRODU CT(--(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))
array entered (ctrl+shift+enter)

Provided all the numbers are clustered together and not interspersed between
text at different positions






"Rich Hayes" wrote in message
...
In column A I have 1000 rows of text.Within each line of text in this
column
is a 5 digit number that is at different points within the text. Is there
any
way I can extract the 5 digit number and have it in column B alongside the
text it came from?

e.g. some text is dfgjdfgdfgd14532sfddfgdfg
other text is dfgsdfgsdfgdfgdfgdfgdf14321
i.e totally random text but with a number in there.

If it was always at the end of the text i'd simply do a formula
=RIGHT(A1,5)
However it's not that simple.

Any help is greatly appreciated

Regards

Rich




  #4   Report Post  
Domenic
 
Posts: n/a
Default

If you're number is always 5 digits in length, try...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),5)+0

Otherwise...

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),ROW(A$1:INDEX(A:A,LEN(A1)))))

Hope this helps!

In article ,
"Rich Hayes" wrote:

In column A I have 1000 rows of text.Within each line of text in this column
is a 5 digit number that is at different points within the text. Is there any
way I can extract the 5 digit number and have it in column B alongside the
text it came from?

e.g. some text is dfgjdfgdfgd14532sfddfgdfg
other text is dfgsdfgsdfgdfgdfgdfgdf14321
i.e totally random text but with a number in there.

If it was always at the end of the text i'd simply do a formula =RIGHT(A1,5)
However it's not that simple.

Any help is greatly appreciated

Regards

Rich

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
can you find specific text in a string ignoring any other text chriscp Excel Discussion (Misc queries) 1 September 18th 05 09:54 PM
making a number be text Fredrated Excel Worksheet Functions 2 June 1st 05 02:37 AM
Finding Specific Text in a Text String Peter Gundrum Excel Worksheet Functions 9 April 10th 05 07:21 PM
Read Text File into Excel Using VBA Willie T Excel Discussion (Misc queries) 13 January 8th 05 01:37 AM
Formatting a cell as "text" in the number catagory. Ed Excel Worksheet Functions 3 December 7th 04 08:12 PM


All times are GMT +1. The time now is 10:00 PM.

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

About Us

"It's about Microsoft Excel"