2010hi,
what version of excel are you using?
M.
i have numbers
like
234-1
1-1
12/a
101a
out put i need
234
1
12
101
other than number
sub number i don't need
subnumber are divided by /, -,.,\ ,text etc.,
If a cell is empty that will return a #VALUE! error. Just needs a small tweak to account for that:Maybe this
A B
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>234-1</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64>234</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1-1</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>12/a</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>101a</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>101</TD></TR></TBODY></TABLE>
Array-formula in B1
=MAX(IFERROR(1*LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),""))
confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)
copy down
M.
If a cell is empty that will return a #VALUE! error. Just needs a small tweak to account for that:
=MAX(IFERROR(1*LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),{""}))
In this application we have to force the blank "" to be evaluated as an array.
=MAX("text") = #VALUE!
=MAX({"text"}) = 0
You've probably learned/read that the MAX function ignores text yet:Biff,
Tks for pointing this out. New for me - why MAX({"text"}) works ?
M.
You've probably learned/read that the MAX function ignores text yet:
=MAX("text")
Returns an error so it IS NOT ignoring text!
how to remove duplicat data 501, 491 comming more number of time in dataYou've probably learned/read that the MAX function ignores text yet:
=MAX("text")
Returns an error so it IS NOT ignoring text!
The MAX function will ignore text but only if it's part of an array or a cell reference.
In your original formula the blank (an empty text string) is not part of an array or a cell reference thus the error.
It may be better to just test the cell for being empty first as 0 could be an otherwise valid result:
=IF(A1="","",...