strip characters from a "number" like string

[skiing]

Hello

Could someone advise as to how to strip characters from a formula
resulting string which could look like the following? ( some of the
result would actually be numbers while others are number/character
combo's - see below for examples)

0002
0124
0125-A
1566A-A

Thank you for any assistance you can offer. !

 

[Pete_UK]

If the "numbers" are always the first 4 characters, as in your
example, you can extract them and turn them into proper numbers with
this:

=LEFT(A1,4)*1

assuming they are in column A - format the cell as General or as
Number, then copy down.

Hope this helps.

Pete

 

[Bob I]

For best replies, please provide examples of the desired end result.

 

[skiing]

Thank you Pete for your response

Unfortunally the leading numbers can vary - could be like 45A-A, 136B-
AB, 1888-BA

End Results - do not care if the end results are numbers or formatted
as text - just simply need to strip the characters at the right side
and leave the rest.

 

[Rick Rothstein \(MVP - VB\)]

If the number of digits can vary, then there are two possibilities... either
the leading digits are the **only** digits in the cell (no matter how many
of them there are), in which case this formula will do what you need...

=LEFT(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1))))

The other possibility is there can be other digits in the cell (after the
non-digits that follow the leading digits), but you only want the leading
digits themselves, in which case this formula should do what you want...

=LEFT(A1,MIN(SEARCH({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-1)

Rick


Thank you Pete for your response

Unfortunally the leading numbers can vary - could be like 45A-A, 136B-
AB, 1888-BA

End Results - do not care if the end results are numbers or formatted
as text - just simply need to strip the characters at the right side
and leave the rest.

 

[Ron Rosenfeld]

Hello

Could someone advise as to how to strip characters from a formula
resulting string which could look like the following? ( some of the
result would actually be numbers while others are number/character
combo's - see below for examples)

0002
0124
0125-A
1566A-A

Thank you for any assistance you can offer. !

You still haven't given any examples of the results; nor have you specified
which characters you wish to remove (or not return).

For example, given the value 1566A-A; what would you like the result of the
"stripped" string to look like?

1566
1566A
1566A-

????

To just return the leading numbers:

=LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))



--ron

 

[ACarella]

Hi Ron:
I need to extract the first and second characters to the left of the decimal
and/or if there is no decimal, the Alpha (which is the prhase family)

For example

13.40015 13
4.100021 4
N03.10021 3
OTHTOX10 OTHTO
PENE0008 PENE
RPEF0003 RPEF

can you help?
Thank you, Arlene

 

[ACarella]

Hi Rich:
Could you help me with this?

I need to extract the first and second characters to the left of the decimal
and/or if there is no decimal, the Alpha (which is the prhase family)

For example

13.40015 13
4.100021 4
N03.10021 3
OTHTOX10 OTHTO
PENE0008 PENE
RPEF0003 RPEF

can you help?
Thank you, Arlene

 

[Ron Rosenfeld]

Hi Ron:
I need to extract the first and second characters to the left of the decimal
and/or if there is no decimal, the Alpha (which is the prhase family)

For example

13.40015 13
4.100021 4
N03.10021 3
OTHTOX10 OTHTO
PENE0008 PENE
RPEF0003 RPEF

can you help?
Thank you, Arlene

Assumptions:

1. You have a typo above, and

OTHTOX10 should result in OTHTOX

2. The Alpha always precedes the numbers
3. The characters preceding the decimal are always digits.
4. There will not be more than two digits prior to the decimal

Then the following **array** entered formula should do the trick.

To **array** enter a formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula if you did it correctly.


=IF(ISERR(FIND(".",A1)),LEFT(A1,-1+MATCH(TRUE,ISNUMBER(
-MID(A1,ROW(INDIRECT("1:255")),1)),0)),INT(LOOKUP(
9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&
"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))))

--ron

 

[Rick Rothstein \(MVP - VB\)]

Assuming your 4th example should have been OTHTOX instead of OTHTO, then
this formula should do what you want...

=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))

Rick

 

[Rick Rothstein \(MVP - VB\)]

Just to make sure you do not mix Ron's instructions for his formula with my
formula... my formula is NOT an array-entered one like Ron's is, so you
commit it normally, using just the Enter key.

Rick

 

[ACarella]

Hi Ron:
You are correct. I had a typo. it is the result you mentioned.
I will try your formula.
Thank you.

 

[ACarella]

Hi Ron:
Thank you so much. This formula worked for all the KeyCodes that contained
number or alphanumeric and had a decimal. However, it returned N/A for those
like the following that have no decimal.

OTHTOX10 OTHTOX
what do you think?

 

[Ron Rosenfeld]

Hi Ron:
Thank you so much. This formula worked for all the KeyCodes that contained
number or alphanumeric and had a decimal. However, it returned N/A for those
like the following that have no decimal.

OTHTOX10 OTHTOX

what do you think?

I think you did not follow the instructions to hold down <ctrl><shift> while
you hit <enter>.

If you did not enter the formula as an **array** formula, you would get the
result you describe.

If you do enter it as an **arrray** formula, then it should give you the
desired result.

Does the formula, when viewed in the formula bar, show the braces {...} around
it?
--ron

 

[ACarella]

I am sorry Ron.
Let me do it again.

I think you did not follow the instructions to hold down <ctrl><shift> while
you hit <enter>.

If you did not enter the formula as an **array** formula, you would get the
result you describe.

If you do enter it as an **arrray** formula, then it should give you the
desired result.

Does the formula, when viewed in the formula bar, show the braces {...} around
it?
--ron

 

[Rick Rothstein \(MVP - VB\)]

Ron has responded with why you are getting incorrect results with his
formula... I was just curious, though, have you looked at the formula I
posted yet? I don't mind if you use Ron's solution over mine, I just would
like you to at least consider it before locking down on a method to handle
your problem... it has a few things going for it over the formula Ron posted
(sorry Ron)... it is shorter, it uses less function calls, it is
non-volatile and only needs the Enter key to commit it.

Rick

 

[ACarella]

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.

 

[ACarella]

No, I didn't see it. I went to the bottom of the list.
Let me check it out. Sorry Rick. I think I sent you another email as well
regarding spreadsheets. Thank you for your help.

 

[ACarella]

I'm going to check this out. And I did get them both mixed up.

 

[Harlan Grove]

If the number of digits can vary, then there are two possibilities...
either the leading digits are the **only** digits in the cell (no
matter how many of them there are), in which case this formula will
do what you need...

=LEFT(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1))))

....

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)