• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Move cells to same column

H

hhgoh

New Member
Hi there,

I have an excel file which is exported from an application as illustrated below. How do it put cell A1,B2& C3 to the same column. Is there a macro or function which can use to automate this process. Ans the actual worksheet contain bulk of data which is almost impossible to do it manually.


Please note that $1,$2,$3 are on different columns and different rows.


A B C

1 $1

2 $2

3 $3


TIA.
 
Hi Hui,

The result became 123, what I wanted to achieve is to remain the row, just to align them to the same column.
 
hhgoh

If you have $1 in A1, $2 in B2 and $3 in C3

Then use


=+CONCATENATE(A1,B2,C3)


But this won't be able to be copied if the pattern of the layout of the cells is different
 
sorry if i dont express clearly, My spreadsheet have hundreds of rows, each row in the spreadsheet is an individual record,when this file was exported, some fields in the records are not in one column. what I wanted to do is to align those fields which are not on the same column. e.g. balance supposed to be on column B, but somehow the balance field for some records are in col. C, I just want find an easier way to move them back to col A.


Thank you for your effort.
 
hhgoh

If you insert a new Column D

and insert a formula in D1 or D2 where ever your data starts

=+CONCATENATE(A1,B1,C1)

that will join all the values in A1, B1 and C1,

and doesn't matter if the cells have values or text or are blank


Copy the Cell down to the bottom of your data

Copy Column D and Paste over the top of itself as values

You can now Delete Columns A, B & C, leaving the joined values in Column A


If importing has added any leading or trailing spaces you can fix that with the following variation

=+CONCATENATE(TRIM(A4),TRIM(B4),TRIM(C4))
 
If the imported data has a text value followed by the balance

you can now use the Text to Columns function on the new column A


in Excel 2003 Data, Text to Columns...

in Excel 2007+ Data Ribbon, Text to Columns...


and separate the the text from the balances using the appropriate separator.
 
You must log in or register to reply here.
Back
Top