Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jordan
 
Posts: n/a
Default formula to analize info in a cell

I have a cell that has a mix a text and numbers. I need to know how many of
the charaters are text and how many are numbers. It usually is something
like JDR89765 or JD5674, meaning that the text is on the left side. If
possible I would like to read from left to right and get a count of how many
text char there are before the first number.

Any help will be greatly appreciated.

Thanks in advance.
  #2   Report Post  
N Harkawat
 
Posts: n/a
Default

=LEN(A1)-SUMPRODUCT(--ISNUMBER(-RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

will tell you the number of text characters in the cell


"Jordan" wrote in message
...
I have a cell that has a mix a text and numbers. I need to know how many
of
the charaters are text and how many are numbers. It usually is something
like JDR89765 or JD5674, meaning that the text is on the left side. If
possible I would like to read from left to right and get a count of how
many
text char there are before the first number.

Any help will be greatly appreciated.

Thanks in advance.



  #3   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

One way, assume the string is in A1

=MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1)),0)

entered with ctrl + shift & enter,

and to get the number of of numbers just use the cell result, assume you put
that formula in B1, in C use

=LEN(A1)-B1

note that if there are spaces they will be counted so you could wrap each
cell refof in the cell with TRIM


=MATCH(TRUE,ISNUMBER(--MID(TRIM(A1),ROW(INDIRECT("1:"&LEN(TRIM(A1))))+1,1 )),0)

then

=TRIM(LEN(A1)-B1 for the numbers


Regards,

Peo Sjoblom

"Jordan" wrote:

I have a cell that has a mix a text and numbers. I need to know how many of
the charaters are text and how many are numbers. It usually is something
like JDR89765 or JD5674, meaning that the text is on the left side. If
possible I would like to read from left to right and get a count of how many
text char there are before the first number.

Any help will be greatly appreciated.

Thanks in advance.

  #4   Report Post  
Harlan Grove
 
Posts: n/a
Default

N Harkawat wrote:
=LEN(A1)-SUMPRODUCT(--ISNUMBER(-RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

will tell you the number of text characters in the cell

....

Not reliably. If A1 contained JDR89765X, your formula returns 9 rather
than 4.

An alternative that works no matter how decimal numerals and other
characters are scrambled is

=SUMPRODUCT(LEN(SUBSTITUTE(G2,{0;1;2;3;4;5;6;7;8;9 },"")))-9*LEN(G2)

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 a Formula in Cell X modify Cell Y? alMandragor Excel Discussion (Misc queries) 7 February 10th 05 10:51 PM
Display answer only in another cell of one containing a formula Mally Excel Discussion (Misc queries) 5 January 21st 05 02:07 PM
looking for a formula Amanda Excel Worksheet Functions 5 January 5th 05 08:37 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM
How do I do math on a cell name in formula? Mark Mulik Excel Worksheet Functions 3 November 23rd 04 04:43 PM


All times are GMT +1. The time now is 06:41 AM.

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"