×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel column join

Excel column join

Excel column join

(OP)
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!)

RE: Excel column join

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.

RE: Excel column join

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources