Need excel expert

[kashif.special2005]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
In a cell there is alphanumeric value I wanted to know the alpha character position.<o></o>

Thanks,

Kashif.

 

[Jonmo1]

Is there only 1 alpha character?
If there are multiple, do you want the first one?

 

[Darren Bartrup]

Do you mean, for example you have a string of characters - say 'A1B3' and you want to find where 1 is in the string (position 2).

If A1B3 is in cell A1, then the formula below will find the first occurrence of 1.

=FIND("1",A1)

Edit - after reading other replies, I don't think this is what you're after. I take it you don't know what the first alpha character will be. I really should read these posts more thoroughly.

 

[sous2817]

From http://www.cpearson.com/excel/stringformulas.aspx

Position Of First Non-Digit In A String

This formula will return the position of the first non-numeric character in the string in cell A1.

=IF(LEN(A1)=0,0,MIN(IF(1*ISNUMBER(1*MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))=0,ROW(INDIRECT("A1:A"&LEN(A1))),LEN(A1)+1)))*(ISNUMBER(A1)=FALSE)


May be worthwhile to have a look on that page for more details.

 

[kashif.special2005]

in cell A1 32A56B, I wanted to first alpha character value position. like for cell A1 Result would be 3.


Thanks,
Kashif.

 

[sous2817]

Missed the last part of the link:

This is an array formula so you must enter it with CTRL SHIFT ENTER rather than just ENTER. If cell A1 is empty, the formula returns 0.

 

[Jonmo1]

Try this array formula entered with CTRL + SHIFT + ENTER

=MATCH(1,--ISERROR(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1)+0),0)*(LEN(A1)>0)

 

[kashif.special2005]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
The formula is not giving currect answer like in cell I have A1000 and when I am applying this formula
<o> </o>
=IF(LEN(B3)=0,0,MIN(IF(1*ISNUMBER(1*MID(B3,ROW(INDIRECT("B2:B"&LEN(B3))),1))=0,ROW(INDIRECT("B2:B"&LEN(B3))),LEN(B3)+1)))*(ISNUMBER(B3)=FALSE)
<o> </o>
It is giving me 6.
<o> </o>
<o> </o>
Thanks,
Kashif.<o></o>

 

[sous2817]

This is an array formula so you must enter it with CTRL SHIFT ENTER rather than just ENTER.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">32A56B</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">{=IF(<font color="Blue">LEN(<font color="Red">A1</font>)=0,0,MIN(<font color="Red">IF(<font color="Green">1*ISNUMBER(<font color="Purple">1*MID(<font color="Teal">A1,ROW(<font color="#FF00FF">INDIRECT(<font color="Navy">"A1:A"&LEN(<font color="Blue">A1</font>)</font>)</font>),1</font>)</font>)=0,ROW(<font color="Purple">INDIRECT(<font color="Teal">"A1:A"&LEN(<font color="#FF00FF">A1</font>)</font>)</font>),LEN(<font color="Purple">A1</font>)+1</font>)</font>)</font>)*(<font color="Blue">ISNUMBER(<font color="Red">A1</font>)=FALSE</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Jonmo1]

In that formula,
=IF(LEN(B3)=0,0,MIN(IF(1*ISNUMBER(1*MID(B3,ROW(INDIRECT("B1:B"&LEN(B3))),1))=0,ROW(INDIRECT("B1:B"&LEN(B3))),LEN(B3)+1)))*(ISNUMBER(B3)=FALSE)

DO NOT change the Highlighted parts, they are not relevant to your data.
All other B3's should be adjusted according to your data.


As well as the formula I posted
DO NOT change the Highlighted parts, they are not relevant to your data
=MATCH(1,--ISERROR(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1)+0),0)*(LEN(A1)>0)


Make sure you're entering with CTRL + SHIFT + ENTER