Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 6th 11, 01:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 30
Default 1st numer in string

I need to locate (=find maybe) the first occurance of a number but
starting from the last position of text because the first couple of
characters in the string may be numbers. Looks something like this
and is not fixed columns.

xxxxxxxx text here xxxxxxx
1/3 this is the text portion 11 22 33 44 55 66

Need to find the text position of the first 1 in 11.


Thanks

  #2   Report Post  
Old December 6th 11, 08:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default 1st numer in string

On Tue, 06 Dec 2011 07:01:59 -0600, Jack Deuce wrote:

I need to locate (=find maybe) the first occurance of a number but
starting from the last position of text because the first couple of
characters in the string may be numbers. Looks something like this
and is not fixed columns.

xxxxxxxx text here xxxxxxx
1/3 this is the text portion 11 22 33 44 55 66

Need to find the text position of the first 1 in 11.


Thanks


I'm not sure exactly what you want, but you should be able to work with this:

The last non-number, non-space character is given by the formula:

=LOOKUP(2,1/((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<" ")*ISERR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRE CT("1:1000")))

The "1000" at the end just needs to be any number longer than the length of the string.

So to find the position of the first "1", with your text string in A1, you can use:



=FIND(1,A1,LOOKUP(2,1/((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<" ")*ISERR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRE CT("1:1000"))))

  #3   Report Post  
Old December 6th 11, 08:39 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2011
Posts: 621
Default 1st numer in string

Ron

How do you come up with these<g


Gord

On Tue, 06 Dec 2011 15:20:24 -0500, Ron Rosenfeld
wrote:

On Tue, 06 Dec 2011 07:01:59 -0600, Jack Deuce wrote:

I need to locate (=find maybe) the first occurance of a number but
starting from the last position of text because the first couple of
characters in the string may be numbers. Looks something like this
and is not fixed columns.

xxxxxxxx text here xxxxxxx
1/3 this is the text portion 11 22 33 44 55 66

Need to find the text position of the first 1 in 11.


Thanks


I'm not sure exactly what you want, but you should be able to work with this:

The last non-number, non-space character is given by the formula:

=LOOKUP(2,1/((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<" ")*ISERR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRE CT("1:1000")))

The "1000" at the end just needs to be any number longer than the length of the string.

So to find the position of the first "1", with your text string in A1, you can use:



=FIND(1,A1,LOOKUP(2,1/((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<" ")*ISERR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRE CT("1:1000"))))

  #4   Report Post  
Old December 7th 11, 12:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default 1st numer in string

On Tue, 06 Dec 2011 12:39:10 -0800, Gord Dibben wrote:

Ron

How do you come up with these<g


Gord


Thanks, Gord. But there is an error in the algorithm that makes it not work if the string does not contain a letter. <sigh Back to the drawing board.
  #5   Report Post  
Old December 7th 11, 01:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default 1st numer in string

On Tue, 06 Dec 2011 07:01:59 -0600, Jack Deuce wrote:

I need to locate (=find maybe) the first occurance of a number but
starting from the last position of text because the first couple of
characters in the string may be numbers. Looks something like this
and is not fixed columns.

xxxxxxxx text here xxxxxxx
1/3 this is the text portion 11 22 33 44 55 66

Need to find the text position of the first 1 in 11.


Thanks


Slight modification to handle the case where there are NO alphabetical letters in the string:

=FIND(B1,A1,LOOKUP(2,1/((MID("z"&A1,ROW(
INDIRECT("1:"&LEN("z"&A1))),1)<" ")*
ISERR(-MID("z"&A1,ROW(INDIRECT("1:"&LEN("z"&A1))),1))),
ROW(INDIRECT("1:1000"))))



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
same numer/character at beginning of cells in row MGD Excel Discussion (Misc queries) 2 March 12th 10 05:06 AM
Formula first time numer appears Mrover Excel Worksheet Functions 3 February 8th 08 03:37 PM
Find first numer in a Column Two-Canucks Excel Worksheet Functions 1 May 1st 06 07:23 PM
Reomving a set numer of characters from a text string ado1963 Excel Discussion (Misc queries) 3 January 10th 06 02:33 PM
Can I change line numer or column name Dave Bonneville Excel Discussion (Misc queries) 2 November 1st 05 07:29 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017