 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2006Extract Certain character from string
How do you extract a certain character from right to left? This is so simple, yet finding any help code takes me to a million other examples. Thanks. 1164NB1 How do I extract the "NB" together? mid("1164NB1", 5, 2) has a value of "NB" > How do you extract a certain character from right to left? This is so > simple, yet finding any help code takes me to a million other examples. [quoted text clipped - 3 lines] > > How do I extract the "NB" together? Thanks! > mid("1164NB1", 5, 2) has a value of "NB" > [quoted text clipped - 5 lines] > > > > How do I extract the "NB" together? Oops, thanks Cliff - I was off by one Declare str1 as a string Set str1 = 1164NB1 as your string value then use the MID function: MID(str1,4,2) This will grab and return the 4th and 5th characters of your string In the worksheet, pull-down: Edit > Find and then enter NB, leave the replace field blank and click replace. You should see: 11641 In VBA (using the Recorder) Sub Macro1() ActiveCell.Replace What:="NB", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Find(What:="NB", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub  SignatureGary''s Student > How do you extract a certain character from right to left? This is so > simple, yet finding any help code takes me to a million other examples. [quoted text clipped - 3 lines] > > How do I extract the "NB" together? Not sure where my post went, but I'll try again. Besides the Mid function, you could also use RegExp (do a google search) or a simple loop, such as .. Code: -------------------- Option Explicit Sub TestMePlease() MsgBox RemoveNumbers("1164NB1") End Sub Function RemoveNumbers(strVal As Variant) As String Dim i As Long, tmp As String On Error Resume Next For i = 1 To Len(strVal) Select Case Asc(UCase(Mid(strVal, i, 1))) Case 65 To 90 tmp = tmp & Mid(strVal, i, 1) End Select Next i If Len(tmp) > 0 Then RemoveNumbers = tmp Else RemoveNumbers = "No Text" End If End Function -------------------- HTH Signaturefirefytr >How do you extract a certain character from right to left? This is so >simple, yet finding any help code takes me to a million other examples. [quoted text clipped - 3 lines] > >How do I extract the "NB" together? Your question is not clear. What do you want to do with "extracted" character? Should your result be 11641 or should your result be NB? Or something else? You can certainly remove NB from the string (is that what you mean by extract?) with the SUBSTITUTE function: =SUBSTITUTE("1164NB1","NB","")--> "11641" but I'm thinking you want something more general. You can substitute cell references for any of the arguments in the SUBSTITUTE function. --ron Ron What a great little function, I've never come accross that one. somethinglikeant >Ron > >What a great little function, >I've never come accross that one. > >somethinglikeant It has many uses. --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.