Help with functions

[JMay]

I have cells B10:B50 with text (samples) as follows:

B10 MLC\Trans 4110 Cash Log
B11 MLC\Marion 4131 Log CC
B12 MLC\Trans 4128 Log Disc
B13 MLC\Trans Jan04 LockBox

I am wanting to build formulas
in C10:C50 to Extract only the
4XXX (the four digit numbers
always beginning with 4)

C10 4110
C11 4131
C12 4128
C13 <<< (Leave Blank)

Also wanting to build formulas
in D1050 Which apply ONLY to
Numeric Text Values in Col C, such as
C10, C11, C12,, but NOT C13

to Read and Furnish
the following:

If B10 contains "CC" then Provide "VM"
If B10 contain "Disc" then provide "DS"
Other wise if neither Provide "RG"

I've been trying to get this going now for a couple hours without success.
Anybody up for giving it a shot? Would be Much appreciated...

 

[ksnapp]

do to inconsistentcies with your data I don't think work sheet funciton
are going to help you with this. The text functions that seem usefu
to me all require you know the exact number of characters before o
after the data you want to keep. In Your examples those numbers vary.
Look up the text functions if you haven't already.

Are you extracting this data from somplace, you might be able to chang
how it is imported to put each work in a different column then you ca
just run with it.

sorry I can't help mor

 

[Guest]

Jmay

Assuming all digits are 4 in length

Highlite column B, Data>Text to Columns> delimited>tick space and Tab>finis

Then in C10 type this formula

=IF(B10<10000,B10,""

and copy dow

The 10000 negates all dates that are in column B and makes the cell blan

A little messy but hpoefully the right resul

Alby

 

[Don Guillett]

This should help
Sub fixrange()

For Each c In Range("b10:b50")
If UCase(Right(c, 2)) = "CC" Then
c.Offset(, 2) = "VM"
ElseIf UCase(Right(c, 4)) = "DISC" Then
c.Offset(, 2) = "DS"
Else
c.Offset(, 2) = "RM"
End If
x = InStr(c, "4")
If Mid(c, x + 1, 1) <> " " Then c.Offset(, 1) = Mid(c, x, 4)
Next
End Sub

 

[JMay]

Yeah Don, Thanks a lot
I'm anxious to apply this code.
Will get to it soon,
Thanks again
Jim

 

[JMay]

Don:
Just finished testing out your code, it works g-r-e-a-t !!
Tks again,
Jim May

 

[Don Guillett]

glad to help