other than number

G

gsrikanth

Board Regular
Joined
Jan 7, 2012
Messages
210
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.,
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Maybe this

A B
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>234-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>234</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> 1-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>12/a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>101a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 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.
 
Upvote 0
gsrikanth said:
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.,
Click to expand...

Control+shift+enter, not just enter:

=IF(ISNUMBER($A2),$A2,LEFT($A2,MATCH(1,1-ISNUMBER(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)+0),0)-1)+0)
 
Upvote 0
Marcelo Branco said:
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.
Click to expand...
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
 
Upvote 0
T. Valko said:
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
Click to expand...

Biff,

Tks for pointing this out. New for me - why MAX({"text"}) works ?

I think another adjustment is necessary

In the case the cell has 1-1 Excel is taking it as a date and returning 40909, so i had to put a space in front of.

So i think this formula is better(including your suggestion)

=MAX(IFERROR(1*LEFT(" "&A1,ROW(INDIRECT("1:"&LEN(" "&A1)))),{""}))

M.
 
Upvote 0
Marcelo Branco said:
Biff,

Tks for pointing this out. New for me - why MAX({"text"}) works ?
M.
Click to expand...
You've probably learned/read that the MAX function ignores text yet:

=MAX("text")

Returns an error so it IS NOT ignoring text! :confused:

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="","",...
 
Upvote 0
T. Valko said:
You've probably learned/read that the MAX function ignores text yet:

=MAX("text")

Returns an error so it IS NOT ignoring text! :confused:
Click to expand...

I was baffled exactly by this

But with your explanation, now, the things are crystal clear for me.

Thank you very much

M.
 
Upvote 0
T. Valko said:
You've probably learned/read that the MAX function ignores text yet:

=MAX("text")

Returns an error so it IS NOT ignoring text! :confused:

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="","",...
Click to expand...
how to remove duplicat data 501, 491 comming more number of time in data
http://www.msofficeforums.com/attachment.php?attachmentid=1997&d=1329492638
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Dynamic dropdown list based on database
Replies
3
Views
196
Winnetou
W
C
  • Question
Running Data Refresh Macro, then Table Update Macro, Back-to-Back?
2
Replies
11
Views
594
Alex Blakenburg
A
A
  • Solved
Excel formula does not return expected result when encountering a cell that includes text in a formula reference
2
Replies
11
Views
632
Joe4
Joe4
R
  • Solved
VBA: Merging columns based on header
2
Replies
12
Views
630
ruinedelf
R
C
  • Question
Dated if Formula
Replies
3
Views
220
kvsrinivasamurthy
K

Forum statistics

Threads
1,215,092
Messages
6,123,063
Members
449,090
Latest member
fragment

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top