other than number

gsrikanth · #1 02-17-2012, 07:06 AM

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.,

Colin Legg · #2 02-17-2012, 08:06 AM

Since there could be many non-numeric characters, the easiest option is to write a VBA UDF which will parse the numbers from the start of each string for you. This has been done lots of times before, so I did a quick search and found this function written by Harlan Grove:

Code:

Function DigitsFirstID(s As String) As String
  'Harlan Grove, worksheet.functions, 2003-10-20
  'extract first string of  digits,  based on
  '-- http://google.com/groups?threadm=_RK...newsranger.com
    Dim i As Long, j As Long, n As Long
    n = Len(s)
    i = 1
    Do While i <= n And Mid(s, i, 1) Like "[!0-9]"
        i = i + 1
    Loop
    j = i + 1
    Do While j <= n And Mid(s, j, 1) Like "[0-9]"
        j = j + 1
    Loop
    DigitsFirstID = Mid(s, i, j - i)
End Function

This needs to be copied and pasted into a standard code module in your workbook, and then you can use a formula such as this to extract the number:

=DigitsFirstID(A2)

I realise that VBA may be new to you so I have attached an example.

gsrikanth · #3 02-17-2012, 08:31 AM

501 is repeating
491 is repeating
only that get once

Colin Legg · #4 02-17-2012, 08:47 AM

Quote:

501 is repeating
491 is repeating
only that get once

Your sample file is now different to the original and it contains information which I do not understand. I also do not understand what "501 is repeating only get that once" means. There were no 501 numbers in your original file.

Unless you can explain your question clearly I will not be able to help you.

I also notice that you have been given several answers to this same question over at MrExcel.
http://www.mrexcel.com/forum/showthread.php?t=614432

gsrikanth · #5 02-17-2012, 08:55 AM

Quote:

Originally Posted by colin legg

your sample file is now different to the original and it contains information which i do not understand. I also do not understand what "501 is repeating only get that once" means. There were no 501 numbers in your original file.

Unless you can explain your question clearly i will not be able to help you.

I also notice that you have been given several answers to this same question over at mrexcel.
http://www.mrexcel.com/forum/showthread.php?t=614432

data is like this
1 4 a
1 - b
1 - c
2 5 d
3 7 e
1 4 a
1 - b
1 - c
4 8 f
5 2 g
1 4 a
1 - b
1 - c
6 1 h

in this 1 one repeating like my data i want to delete repeated

Colin Legg · #6 02-17-2012, 09:05 AM

Quote:

data is like this
1 10 a
1 b
1 c
2 5 d
3 7 e
1 10 a
1 b
1 c
4 8 f
5 2 g
1 10 a
1 b
1 c
6 1 h

in this 1 one repeating like my data i want to delete repeated

So, you are saying that the following values are repeated:

1 10 a
1 b
1 c
2 5 d
3 7 e
1 10 a
1 b
1 c
4 8 f
5 2 g
1 10 a
1 b
1 c
6 1 h

So you want to delete the red ones. Is that correct?

gsrikanth · #7 02-17-2012, 09:06 AM

Quote:

Originally Posted by colin legg

so, you are saying that the following values are repeated:

1 10 a
1 b
1 c
2 5 d
3 7 e
1 10 a
1 b
1 c
4 8 f
5 2 g
1 10 a
1 b
1 c
6 1 h

so you want to delete the red ones. Is that correct?

yes extractly see in the attachment gap and all other then names

Colin Legg · #8 02-17-2012, 09:09 AM

Okay. An easy way to extract distinct values is to put a column heading in Row 1 of that column and then use the advanced filter to extract unique values. Extract it to a different range and the results will not contain the duplicates.

gsrikanth · #9 02-17-2012, 09:14 AM

Quote:

Originally Posted by Colin Legg

Okay. An easy way to extract distinct values is to put a column heading in Row 1 of that column and then use the advanced filter to extract unique values. Extract it to a different range and the results will not contain the duplicates.

it is not useful to me (there is a gaps)
firstly coming number data should then after not
plz take my attachment do it for me

Colin Legg · #10 02-17-2012, 09:20 AM

Quote:

where would we see advanced filter

It depends on which version of Excel are you using? At MrExcel you said you are using Excel 2010 but on your profile here it says you are using Excel 2002? If you give me your version I will give you a step by step guide using the data you posted in post #5.

gsrikanth · #11 02-17-2012, 09:22 AM

Quote:

Originally Posted by Colin Legg

It depends on which version of Excel are you using? At MrExcel you said you are using Excel 2010 but on your profile here it says you are using Excel 2002? If you give me your version I will give you a step by step guide using the data you posted in post #5.

tell me step
i find advanced filter

it is not useful to me (there is a gaps)
firstly coming number data should then after not
plz take my attachment do it for me

i send other file of mine

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
number to text	acki	Excel	1	01-26-2012 07:47 AM
convert to number	gsrikanth	Excel	1	01-09-2012 01:01 AM
number change 11 to 12	uoume	Office	0	10-30-2011 06:33 PM
Mailing: how to make the "page number" in Word is the same as "row number" in excel w	Jamal NUMAN	Word	1	09-03-2011 11:37 AM
Last Number in a Column	paulrm906	Excel	1	12-05-2008 07:51 PM