strip characters from a "number" like string

[Rick Rothstein \(MVP - VB\)]

...
...

Variation on this theme, define a name like NCHARS referring to, say,
255, and a name like seq referring to the formula

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,NCHARS,1))

then use a formula like

=LOOKUP(1,-MID(A1,seq,1),MID(A1,1,seq))

This assumes the numeric substring is always at the beginning of the
string. If it could be in the middle or at the end of the string,
you'd need to use something like

=MID(LOOKUP(1,-MID(E6,seq,1),MID(E6,1,seq)),
MIN(FIND({0;1;2;3;4;5;6;7;8;9},E6&"0123456789")),NCHARS)

Yes, that definitely makes the formula look less intimidating; but then it
might require the inclusion of a mini-lesson on naming in case the poster is
unfamiliar with how to do that. The formula, as posted, of course, requires
nothing extra of the poster other than a simple copy/paste operation.

Just out of curiosity, if you know, is there any speed (efficiency)
advantage to using your suggested name references over using the
include-it-all method I posted? My guess is the answer is "yes" for the
'seq' name as it is repeated twice and probably "no" for the NCHARS name.

Rick

 

[Harlan Grove]

...
....

Just out of curiosity, if you know, is there any speed (efficiency)
advantage to using your suggested name references over using the
include-it-all method I posted? My guess is the answer is "yes" for the
'seq' name as it is repeated twice and probably "no" for the NCHARS
name.

I don't think any speed gains would come from using defined names, but
I believe (can't prove) that the LOOKUP approach is faster.

I use names like seq because they're shorter than ROW(..) expressions,
I avoid hardcoded ranges in ROW(..) expressions because they're
subject to errors when rows are inserted or deleted, and I use names
like NCHARS for flexibility and uniformity.

 

[Ron Rosenfeld]

WOW!!!!
Thank you.

Did you receive another email from me?
This is what it was:

I was wondering if you could help me.

I have the following in Excel 2007:
w8001.xlsx
w346.xlsx
w78.xlsx
w172.xlsx

w8001.xlsx has 8 columns and 8001 rows of data
w346.xlsx has 3 columns and 346 rows of data
w78.xlsx has 3 columns and 78 rows of data
w172.xlsx has 3 columns and 172 rows of data

I need to append the three columns (A, B and C)(Columns do not have the same
name, but the data is the same) of w346, 278 and w172 to the
END of W8001 and the data to append to the appropriate 3 columns.
How do I proceed.

I'm glad you got it working, although Rick's solution is probably more
efficient.

So far as your other problem, why not copy/paste?
--ron

 

[ACarella]

Hu Rick. What do I need to change for the return of sections 1-9 to be 01,
02, 03, 04, 05, 06, 07, 08, 09

Thank you.
Arlene

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure what you are asking. Do you want 1 to become 01, 2 to become
02, 3 to become 03 and so on? Or are you asking to have the string "1-9"
expanded to the single string "01, 02, 03, 04, 05, 06, 07, 08, 09"? If the
latter, can the 1 and 9 be different numbers (such as 3-8)?

Rick

 

[ACarella]

I would like the section that is extracted from the key code to display as
two places, 01, 02, 03 all the way up to section 16 (that is the highest the
numeric sections go)

 

[Rick Rothstein \(MVP - VB\)]

I think this does what you want...

=TEXT(IF(ISNUMBER(FIND(".",A1)),--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),FIND(".",A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))),LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)),"00")

Rick

 

[ACarella]

Yes, Rick. It worked.
Thank you very much and have a nice weekend.