#1
|
|||
|
|||
other than number
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., |
#2
|
||||
|
||||
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 =DigitsFirstID(A2) I realise that VBA may be new to you so I have attached an example. |
#3
|
|||
|
|||
501 is repeating
491 is repeating only that get once |
#4
|
||||
|
||||
Quote:
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 |
#5
|
|||
|
|||
Quote:
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 |
#6
|
||||
|
||||
Quote:
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? |
#7
|
|||
|
|||
yes extractly see in the attachment gap and all other then names
|
#8
|
||||
|
||||
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.
|
#9
|
|||
|
|||
Quote:
firstly coming number data should then after not plz take my attachment do it for me |
#10
|
||||
|
||||
Quote:
|
#11
|
|||
|
|||
Quote:
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 |
Thread Tools | |
Display Modes | |
|
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 |