Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-17-2012, 07:06 AM
gsrikanth gsrikanth is offline other than number Windows XP other than number Office XP
Competent Performer
other than number
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default 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.,
Attached Files
File Type: xlsx Book1.xlsx (15.2 KB, 10 views)
Reply With Quote
  #2  
Old 02-17-2012, 08:06 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline other than number Windows 7 32bit other than number Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Attached Files
File Type: xlsm Book1.xlsm (25.2 KB, 7 views)
Reply With Quote
  #3  
Old 02-17-2012, 08:31 AM
gsrikanth gsrikanth is offline other than number Windows XP other than number Office XP
Competent Performer
other than number
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

501 is repeating
491 is repeating
only that get once
Attached Files
File Type: xlsx BCCC.xlsx (19.8 KB, 12 views)
Reply With Quote
  #4  
Old 02-17-2012, 08:47 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline other than number Windows 7 32bit other than number Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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
Reply With Quote
  #5  
Old 02-17-2012, 08:55 AM
gsrikanth gsrikanth is offline other than number Windows XP other than number Office XP
Competent Performer
other than number
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

Quote:
Originally Posted by colin legg View Post
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
Reply With Quote
  #6  
Old 02-17-2012, 09:05 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline other than number Windows 7 32bit other than number Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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?
Reply With Quote
  #7  
Old 02-17-2012, 09:06 AM
gsrikanth gsrikanth is offline other than number Windows XP other than number Office XP
Competent Performer
other than number
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

Quote:
Originally Posted by colin legg View Post
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
Reply With Quote
  #8  
Old 02-17-2012, 09:09 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline other than number Windows 7 32bit other than number Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
  #9  
Old 02-17-2012, 09:14 AM
gsrikanth gsrikanth is offline other than number Windows XP other than number Office XP
Competent Performer
other than number
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
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
Reply With Quote
  #10  
Old 02-17-2012, 09:20 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline other than number Windows 7 32bit other than number Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
  #11  
Old 02-17-2012, 09:22 AM
gsrikanth gsrikanth is offline other than number Windows XP other than number Office XP
Competent Performer
other than number
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
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
Reply With Quote
Reply

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
other than number 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
other than number 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
other than number Last Number in a Column paulrm906 Excel 1 12-05-2008 07:51 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:15 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft