How to Convert Eastern District Florida to EDF ???

[Jerry Houlihan]

I've been trying to figure out how to write a macro that will take the
contents of one cell "Eastern District Florida" and paste the first letter
of each word into another cell..."EDF"

The number of words will changes at times...for instance:

District North Dakota ---- DND
Central District New Hampshire ---- CDNH

etc.

Can anyone suggest a simple way to do this?

I'd appreciate any help.

Thanks,

Jerry H.

 

[Steve Garman]

I've been trying to figure out how to write a macro that will take the
contents of one cell "Eastern District Florida" and paste the first letter
of each word into another cell..."EDF"

You could try a function like:

Function inits$(s$)
Dim a As Variant, i%, outp$
a = Split(s$)
outp$ = ""
For i% = 0 To UBound(a)
outp$ = outp$ & Left(a(i%), 1)
Next i%
inits$ = outp$
End Function

which should allo you to use "=inits(A1)" in the target cell

 

[Jerry Houlihan]

Thanks for the reply Steve.

I guess I'm way behind in Excel technology because I'm still using macros
to do everything. Can you explain a bit about how to use this formula?
If it makes it easier I have more info about what I'm trying to do.

In Cell K11 I have typed Eastern District Virginia and I want EDVA to
appear in Cell L4.

It would be nice if the function could also handle Distric of Florida and
display DFL

This request is different from my original. I was thinking of using IF
THEN. My fields are limited to the 50 US States and EASTERN WESTERN
NORTHERN SOUTHERN.

So perhaps I could tell Excel to search a cell for "Florida" and if it
finds it then put FL in the target cell. If it finds "California" then
it would put CA in the target. Then I could have it substitute E for
Eastern and etc.

Is this a possibility?

Thanks very much for your time.

jerry h.

 

[Steve Garman]

Thanks for the reply Steve.

I guess I'm way behind in Excel technology because I'm still using macros
to do everything. Can you explain a bit about how to use this formula?
If it makes it easier I have more info about what I'm trying to do.

If you take the function I posted and put it into a module, just as if
it was a macro, you should be able to call it directly from a worksheet
like any other function. place "=inits(A1)" (no quotes) in cell A2 and
A2 will show the initials of what's in A1.

Alternatively, you can just use the function in a macro, as you
originally intended.

In Cell K11 I have typed Eastern District Virginia and I want EDVA to
appear in Cell L4.

Now you've changed the rules, haven't you

It would be nice if the function could also handle Distric of Florida and
display DFL

This request is different from my original. I was thinking of using IF
THEN. My fields are limited to the 50 US States and EASTERN WESTERN
NORTHERN SOUTHERN.

I think a case statement would be easier than IF/THEN

How about someting like this?

Function parseWords$(s$)
Dim a As Variant, i%, wd$, outp$
a = Split(s$)
outp$ = ""
For i% = 0 To UBound(a)
wd$ = a(i%)
Select Case LCase(wd$)
Case "florida": wd$ = "FL"
Case "virginia": wd$ = "VA"
Case Else: wd$ = Left(wd$, 1)
End Select
outp$ = outp$ & wd$
Next i%
parseWords$ = outp$
End Function

Once again, you can either use this in a formula or in a macro.

You'll need to put at least another 48 case lines in, of course. I don't
know many state abbreviations myself, being a foreigner.

 

[Jerry Houlihan]

Thanks again for taking the time to write that Steve.
I appreciate it. I'm going to study this over the weekend and put it to
good use.

Jerry