How to Convert Eastern District Florida to EDF ???

  • Thread starter Thread starter Jerry Houlihan
  • Start date Start date
J

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.
 
Jerry said:
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"
Click to expand...

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
 
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.
 
Jerry said:
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
 
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
 
Back
Top