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"

"Kou Vang" wrote in message
...
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?

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

--
Gary''s Student


"Kou Vang" wrote:

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?

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


--
firefytr


------------------------------------------------------------------------
firefytr's Profile: http://www.excelforum.com/member.php...fo&userid=6640
View this thread: http://www.excelforum.com/showthread...hreadid=504974

On Wed, 25 Jan 2006 08:53:12 -0800, "Kou Vang"
wrote:

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?

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

Thanks!

"Cliff Carson" wrote:

mid("1164NB1", 5, 2) has a value of "NB"

"Kou Vang" wrote in message
...
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?

Ron

What a great little function,
I've never come accross that one.

somethinglikeant

Oops, thanks Cliff - I was off by one

On 25 Jan 2006 10:40:54 -0800, "somethinglikeant" wrote:

Ron

What a great little function,
I've never come accross that one.

somethinglikeant

It has many uses.


--ron