How can I put values from different columns in a single cell

ADRIANA P
ADRIANA P used Ask the Experts™
on
like this ! sample !!
many-columns-in-one.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi Adriana, you can use below UDF which would be much easier:
Function CONCATRANGE(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
CONCATRANGE = CONCATRANGE & varDelimiter & varTemp
Next
If varDelimiter <> vbNullString Then CONCATRANGE = Mid(CONCATRANGE, 2)
End Function

Open in new window

Then your formula in cell K3 would be:
=CONCATRANGE(A3:J3,",")

Open in new window

Please find attached...
Adriana-many-columns-in-one_v1.xlsm
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Thanks !! Shums but not coma can be !!
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Then change your formula to:
=CONCATRANGE(A4:J4)

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Great ! job !Thnks  Shums
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You're Welcome Adriana! Pleased to help :)
Roy CoxGroup Finance Manager

Commented:
UDF copied from here

If you are using Office 365 there is a new Function called TextJoin

TextJoin_2.png
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Roy_Cox Thanks !! great to learn !! from you !!
Roy CoxGroup Finance Manager

Commented:
This function is available in Excel 2016 if you are an Office 365 subscriber and have the latest version of Office installed. It's also available with Excel Online, Excel Mobile, Excel for iOS, and Excel for Android phones and tablets. If you have Office 365 and can’t see the TEXTJOIN then it’s likely you’re on the Deferred Channel, which hasn’t got  it yet.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Roy_Cox i have just  Excel 2016
Roy CoxGroup Finance Manager

Commented:
I expect the function will be added to 2016 eventually
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
GOOD TO KNOW ! ROY

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial