Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extract numbers from strings
Hi All!
How do I extract a number from a string? Example: cell D64 has a string "Q30 no lockup" in my case, the first char is always a letter and the next two chars form a number I need to extract. When I try =N(right(left(D64,3),2)) it returns 0 I need 30. I need ultimately to find the average of these numbers in the strings running across. Help? THANKS! Stan |
#2
|
|||
|
|||
Hi Stan,
One way =--RIGHT(D4,2) -- HTH RP (remove nothere from the email address if mailing direct) "Stan Altshuller" wrote in message ... Hi All! How do I extract a number from a string? Example: cell D64 has a string "Q30 no lockup" in my case, the first char is always a letter and the next two chars form a number I need to extract. When I try =N(right(left(D64,3),2)) it returns 0 I need 30. I need ultimately to find the average of these numbers in the strings running across. Help? THANKS! Stan |
#3
|
|||
|
|||
it works!
how would one know that? I can not find help on this -- function. amazing :) anyway THANKS for replying so quickly. Stan "Bob Phillips" wrote in message ... Hi Stan, One way =--RIGHT(D4,2) -- HTH RP (remove nothere from the email address if mailing direct) "Stan Altshuller" wrote in message ... Hi All! How do I extract a number from a string? Example: cell D64 has a string "Q30 no lockup" in my case, the first char is always a letter and the next two chars form a number I need to extract. When I try =N(right(left(D64,3),2)) it returns 0 I need 30. I need ultimately to find the average of these numbers in the strings running across. Help? THANKS! Stan |
#4
|
|||
|
|||
-- HTH RP (remove nothere from the email address if mailing direct) "Stan Altshuller" wrote in message ... it works! how would one know that? I can not find help on this -- function. amazing :) anyway THANKS for replying so quickly. Stan "Bob Phillips" wrote in message ... Hi Stan, One way =--RIGHT(D4,2) -- HTH RP (remove nothere from the email address if mailing direct) "Stan Altshuller" wrote in message ... Hi All! How do I extract a number from a string? Example: cell D64 has a string "Q30 no lockup" in my case, the first char is always a letter and the next two chars form a number I need to extract. When I try =N(right(left(D64,3),2)) it returns 0 I need 30. I need ultimately to find the average of these numbers in the strings running across. Help? THANKS! Stan |
#5
|
|||
|
|||
It's the RIGHT that does the work, the -- just ensures it is a number. You
can get the same result with =VALUE(RIGHT(D4,2)) -- HTH RP (remove nothere from the email address if mailing direct) "Stan Altshuller" wrote in message ... it works! how would one know that? I can not find help on this -- function. amazing :) anyway THANKS for replying so quickly. Stan "Bob Phillips" wrote in message ... Hi Stan, One way =--RIGHT(D4,2) -- HTH RP (remove nothere from the email address if mailing direct) "Stan Altshuller" wrote in message ... Hi All! How do I extract a number from a string? Example: cell D64 has a string "Q30 no lockup" in my case, the first char is always a letter and the next two chars form a number I need to extract. When I try =N(right(left(D64,3),2)) it returns 0 I need 30. I need ultimately to find the average of these numbers in the strings running across. Help? THANKS! Stan |
#6
|
|||
|
|||
=N(right(left(D64,3),2))
Instead of using Right & Left, using Mid might be an option: The following would return 30 from "Q30 no lockup" =VALUE(MID(A1,2,2)) or: =--(MID(A1,2,2)) HTH -- Dana DeLouis Win XP & Office 2003 "Stan Altshuller" wrote in message ... Hi All! How do I extract a number from a string? Example: cell D64 has a string "Q30 no lockup" in my case, the first char is always a letter and the next two chars form a number I need to extract. When I try =N(right(left(D64,3),2)) it returns 0 I need 30. I need ultimately to find the average of these numbers in the strings running across. Help? THANKS! Stan |
#7
|
|||
|
|||
The function that is intended for this sort of thing (i.e. you know where the
text begins (2nd char) and how long it is (2 chars) is MID. =--MID(D4,2,2) On Thu, 17 Feb 2005 09:55:09 -0500, "Stan Altshuller" wrote: it works! how would one know that? I can not find help on this -- function. amazing :) anyway THANKS for replying so quickly. Stan "Bob Phillips" wrote in message ... Hi Stan, One way =--RIGHT(D4,2) -- HTH RP (remove nothere from the email address if mailing direct) "Stan Altshuller" wrote in message ... Hi All! How do I extract a number from a string? Example: cell D64 has a string "Q30 no lockup" in my case, the first char is always a letter and the next two chars form a number I need to extract. When I try =N(right(left(D64,3),2)) it returns 0 I need 30. I need ultimately to find the average of these numbers in the strings running across. Help? THANKS! Stan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
How do you extract numbers from a string of chacters in a cell (E. | Excel Worksheet Functions |