Excel column join 1

[Greybeard]

I have a problem with joining cells or columns after splitting them using 'Data – Text to Columns. I need to do this in order to sort on part of the column data e.g. on LastName where FirstName & LastName are entered in the same column.

Using the formula =CellRef&CellRef or =CONCATENATE(Cell Ref,Cell Ref) I get the formula displayed instead of the value.

I have narrowed this down to .txt or .csv files imported into Excel using the import wizard (possibly where I have designated all columns to text format in order not to lose the leading zeros in some of the columns).

Is there a way round this? It only seems to happen on imported files and I think it may be the result of using thetext format option. On the other hand it may be the result of importing some hidden formatting when the file is imported.

One other query – where do I turn off the Autocorrect which converts numbers to dates? I've not found this in Autocorrect, Tools – Options or any reference to the feature in Help (maybe not asked the right question!)

 

[Mala]

THE SECOND QUESTION FIRST:

Excel's Autocorrect feature doesn't take care of this.
Excel converts to dates the following:

a.Cells which are date-formatted - if you enter numbers in them they'll be converted to date format.

b.General format cells in which you make an entry that looks like a date - if u enter '2-2-67' in such a cell it'll be treated as a date entry - u'll see the date number in the formula bar. Once this happens, the date format 'sticks' i.e. subsequent numeric entries will be treated as dates.

This is usually helpful but can be a pain at times when that is NOT what u intend to do...

Try formatting the cells with 'text' formatting if the entries 'look' like dates.

Try formatting the cells with 'General' formatting if you want to show the numbers as they are and the cells are already fomatted with the 'date' format.

YOUR SECOND QUERY:
The cells will show the formula instead of the result ONLY if they are already formatted as 'text' - check the formats of these cells if you find this is true - change them to 'general'.

If this does not take care of the problem, come again on the forum with some more specifics, examples. If u find it too difficult to explain, send the xls to mala_rs_singh@rediffmail.com

Good luck.

 

[tcampbe1]

Interested in another approach? Instead of using "text to columns", make a new column with the following formula in it:

=MID(A1,FIND(" ",A1)+1,20)

Assuming your names are in column A, this finds the space in the name and uses text from that point on to fill the new column. The "20" is arbitrary. Then sort on the new column, and don't touch your original data. The new column can be deleted after the sort.