Ok, I admit this is a weird one. I am manually deleting duplicate
contact records from a CSV file in Excel. Ok, I was trying to REPLACE
ALL in a specific column where I needed no extra spaces but instead of
the one column it replaced everything!!! Without knowing, I saved the
file...

So my question here is how do I get the spaces back where they're
needed like in the Address Field, Company Name field, etc.? Is there a
macro I can do? If so, how? Most of the fields are in title case...
which helps show distinction.

HELP!!! It's due back to my client tomorrow!!!!

If you need to see what I'm talking about ... here's the link:
http://www.gvdesign.com/latta-act.txt


--
kelleychambers
------------------------------------------------------------------------
kelleychambers's Profile: http://www.excelforum.com/member.php...o&userid=25142
View this thread: http://www.excelforum.com/showthread...hreadid=386375

kelleychambers wrote:
Ok, I admit this is a weird one. I am manually deleting duplicate
contact records from a CSV file in Excel. Ok, I was trying to REPLACE
ALL in a specific column where I needed no extra spaces but instead of
the one column it replaced everything!!! Without knowing, I saved the
file...

So my question here is how do I get the spaces back where they're
needed like in the Address Field, Company Name field, etc.? Is there a
macro I can do? If so, how? Most of the fields are in title case...
which helps show distinction.

HELP!!! It's due back to my client tomorrow!!!!

If you need to see what I'm talking about ... here's the link:
http://www.gvdesign.com/latta-act.txt



Well, you could write a macro to stick a space in front of every capital letter
that does not lead an entry I suppose. And one behind every comma or period
which is not followed by a capital.

And of course, from here on you might consider setting your system up to make
nightly backups whether you need them or not.

Good luck....

Bill

I thought a macro could be written... however, my question is... how in
heck do I do it?


--
kelleychambers
------------------------------------------------------------------------
kelleychambers's Profile: http://www.excelforum.com/member.php...o&userid=25142
View this thread: http://www.excelforum.com/showthread...hreadid=386375

kelleychambers wrote:
I thought a macro could be written... however, my question is... how in
heck do I do it?



If you've never done anything with macros, this probably isn't the project to
learn on unless you've got someone geographically close to you to guide you
through it. Lots of little gotchas and nonintuitive things. It may well be
faster for you to do it manually than to set out to learn VBA programming for
this one task. Probably not what you wanted to hear...

Bill

Try this against a copy of your worksheet. You'll still have plenty to fix
manually, but it'll give you a little bit of a headstart:

Option Explicit
Sub testme()

Dim iStr As String
Dim oStr As String
Dim iCtr As Long
Dim Rng As Range
Dim myCell As Range
Dim myChar As String

Application.ScreenUpdating = False

Set Rng = Intersect(Selection, ActiveSheet.UsedRange)

For Each myCell In Rng.Cells
iStr = myCell.Value
If UCase(iStr) = iStr Then
'do nothing
Else
iStr = iStr & "." 'some dummy character
oStr = Left(iStr, 1)
For iCtr = 2 To Len(iStr) - 1
myChar = Mid(iStr, iCtr, 1)

If IsNumeric(myChar) Then
'do nothing
Else
If myChar = "." Then
'do nothing
Else
If myChar = UCase(myChar) Then
oStr = oStr & " "
End If
End If

End If
oStr = oStr & myChar
Next iCtr
myCell.Value = oStr
End If

Next myCell

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out via:

select your range (ctrl-a (twice in xl2003) and then...

tools|macro|macros...
select the macro and click run.

==========
It's too late for this, but when I'm working on an important workbook, I'll save
intermediate copies:

MyImportantWorkBook_2005_07_12.xls
Every day I'll have at least one backup.

And if I'm doing lots of major stuff, I'll even have files like this:

MyImportantWorkBook_2005_07_12__10_04.xls
and
MyImportantWorkBook_2005_07_12__11_30.xls
and
MyImportantWorkBook_2005_07_12__15_45.xls

Disk space is cheap compared to the time it'll take to rebuild the workbook.

Good luck,



kelleychambers wrote:

I thought a macro could be written... however, my question is... how in
heck do I do it?

--
kelleychambers
------------------------------------------------------------------------
kelleychambers's Profile: http://www.excelforum.com/member.php...o&userid=25142
View this thread: http://www.excelforum.com/showthread...hreadid=386375

--

Dave Peterson

Dave, you are absolutely today's hero in my life! That working
brilliantly!!! Now, I'm certainly going to take your advise and start
saving my worksheet in multiple places.... wow, I'm so impressed!!!

Now, I want to learn macros in Excel. I'm a computer person naturally
and think this would come a lot easier than some would think!

::Thank you, thank you, thank you..... Dave, you
rock!!!!::


--
kelleychambers
------------------------------------------------------------------------
kelleychambers's Profile: http://www.excelforum.com/member.php...o&userid=25142
View this thread: http://www.excelforum.com/showthread...hreadid=386375

David McRitchie has some links to tutorials at:
http://mvps.org/dmcritchie/excel/excel.htm#tutorials

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. I think that John Green (and
others) is nice, too (for a second book??). See if you can find them in your
local bookstore and you can choose what one you like best.


kelleychambers wrote:

Dave, you are absolutely today's hero in my life! That working
brilliantly!!! Now, I'm certainly going to take your advise and start
saving my worksheet in multiple places.... wow, I'm so impressed!!!

Now, I want to learn macros in Excel. I'm a computer person naturally
and think this would come a lot easier than some would think!

::Thank you, thank you, thank you..... Dave, you
rock!!!!::

--
kelleychambers
------------------------------------------------------------------------
kelleychambers's Profile: http://www.excelforum.com/member.php...o&userid=25142
View this thread: http://www.excelforum.com/showthread...hreadid=386375

--

Dave Peterson