Have two problems to be solved.

  • Thread starter Thread starter SteveL
  • Start date Start date
S

SteveL

I have an Excel spreadsheet that has two issues I need to
resolve...

1) There is a column of dollar figures that have a space
following the dollar sign. How can I remove the space?

2) Have a f=name field like "John C Doe". I need to
insert a perion after the middle initial. How can I do
that?

Any help much appreciated.

--Steve
 
Hi

1. Check the format of the dollar figures. It might be that they are
formatted to have a space between the sign and the number. Failing that, you
can use Find/Replace with " " in the Find box and leave the Replace box
blank.

2. Are all of the records the same. Do they all have a single middle
initial, or do some have none and some have two?
 
Andy,

#1 is no longer an issue.

But with #2, actually the field looks like "John D" with
no period and I need to insert a period. But sometimes
its just "John" with no middle initial. If that's the
case then nothing has to be done with the field.

--Steve
 
It's a bit of a whopper - but it does the job!

=LEFT(C18,FIND(" ",C18)+1)&"."&MID(C18,FIND(" ",C18)+2,99)
 
Andy,

It works if there is a middle initial but it doesn't work
if there is only a first name. ???

--Steve
 
You can try this.
It's a little long because I added the possibility of there being *middle
names*, which would also *not* need a period.

=IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),A1,IF(FIND(" ",A1,FIND("
",A1)+1)-2=FIND(" ",A1),LEFT(A1,FIND(" ",A1)+1)&"."&MID(A1,FIND("
",A1)+2,25),A1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Andy,

It works if there is a middle initial but it doesn't work
if there is only a first name. ???

--Steve
 
I just tried this latest suggestion and its not adding
the period after a middle initial.

Ideas?

--Steve
 
It's working fine for me.

John Public = John Public
John Q Public = John Q. Public
John Quentin Public = John Quentin Public

Watch out for word-wrap!

--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------



I just tried this latest suggestion and its not adding
the period after a middle initial.

Ideas?

--Steve
 
Back
Top