help formatting columns

[Hugh Mungo]

Hi,

I have been given a spreadsheet which has been dumped from a database, but
the data in the coulmns in not very organised.
I am using Excel 2003 and I am having problems with the following:

1. A lot of the cells have a black solid square at the end. When viewed in
the formula bar it appears as an outline of a square.
I have tried copying it so that I can search and replace but it does not
paste.

2. There is a column that contains dates. However a single cell can contain
server dates and these dates are in different formats
e.g. (10/03/04 9th March 2004 27th Feb 2004)
I only want the chronologically last date in the format dd/mm/yyyy

3. There is a column that contains peoples names. A single cell can contain
many people's names. I just want the first name.

4. There is a coulmn that contains text and dates. Again I just want the
chronologically last date in the format dd/mm/yyyy

I am not sure if some of these are actually possible, but I hope so as there
are over 800 rows and I don't want to go through them all by hand.

Thanks in advance for any help.

 

[kkknie]

Your first problem is probably a carriage return or line feed. T
remove all of these, you need to run a macro.


Code
-------------------
Sub ReplaceChrs()

Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Su
-------------------

Right click the tab and select view code, paste the macro and then ru
it by hitting the F5 key. You may need to run it a second time if i
doesn't delete the square boxes by changing Chr(13) to Chr(10). If i
is not one of those characters, post back and we'll work throug
finding out what the character is.

As for your other problems, the best answer is probably a text t
columns solution. Here's how I would recommend handling it.

Copy the offending column to another tab.
Select the column.
From the Data Menu, select Text to Columns
Either choose delimited or fixed and fool around until you can get onl
the values you want. You may have to choose delimited by a space fo
the names and then concatenate the good ones back together.

Sounds like a lot of work, but it is probably better than manuall
editing 800 rows...

 

[Charlie]

Hi,

I have been given a spreadsheet which has been dumped from
a database, but the data in the coulmns in not very
organised. I am using Excel 2003 and I am having problems
with the following:

I am not that versed in VBA to give an easier answer but
if all else fails try the following

1. A lot of the cells have a black solid square at the
end. When viewed in the formula bar it appears as an
outline of a square. I have tried copying it so that I can
search and replace but it does not paste.

Already answered by kkknie.

2. There is a column that contains dates. However a single
cell can contain server dates and these dates are in
different formats e.g. (10/03/04 9th March 2004 27th Feb
2004) I only want the chronologically last date in the
format dd/mm/yyyy

In your example it appears the last date is the first
series in the string of text. If the last date is in the
format as shown then enter in an adjacent cell. Assuming
Column E contains the dates and the date is in
the "dd,mm,yy" or "dd,mm.yyyy" format. =LEFT(E1,(FIND
(" ",E1)-1)) and copy down. (note the space between
the " "). Next do a copy on the column with the formula
and a paste special, values, on that same column. If
there is a mixture of 04's and 2004's replace all 2004's
with 04, then do another replace with 04 as 2004.

Now for the hard part, it the first date is a mixture of
04's, 2004's, 9th, 1st, 3rd's, and Jan, January do a
search, 2004 and replace with 04. By doing this all years
will be the same. Now replace 04 with "2004".

Still some more work to do. Replace all 'st(space) ', 'nd
(space) ', 'rd(space) ', and 'th(space) ', with '/'.
Replace "January(space)'" with 01/, February(space) with
02/, Jan(space) with 01/ and Feb(space) with 02/. Now you
should have "10/03/2004 09/03/2004 27/02/2004". Highlight
the column and do a Text to Columns under the Data menu.
Delete the unwanted columns.

3. There is a column that contains peoples names. A single
cell can contain many people's names. I just want the
first name.

Again here do a text to column, then concatenate as needed.


4. There is a coulmn that contains text and dates. Again I
just want the chronologically last date in the format
dd/mm/yyyy.

Unfortunately follow the steps In 2.

I am not sure if some of these are actually possible, but
I hope so as there are over 800 rows and I don't want to
go through them all by hand.

Thanks in advance for any help.

It is a lot of work, but if you are good with the keyboard
it is a lot faster then doing if manually.

Charlie O'Neill