I have text in multiple (>20) cells to combine and need to add a space between each cell -
for example,
A1: JOHN
A2: PETE
A3: MARY
combined output: JOHN PETE MARY
While I can literally write out concatenate (a1," ",a2," ",a3," " ...) or a1&" "&a2&" "&a3&" " ...
since there are more than 20 cells of content to concatenate, is there a faster way to do it without typing out all " " ?
Your profile says that you are using 2010. Unfortunately, the easiest way to do this in Excel (the TEXTJOIN() function) would require you to upgrade to 2019 or 2016+365 subscription. (Unless, of course, you have upgraded Excel but have not updated your profile).
How does this task fit into the project? Other spreadsheets (like LibreOffice Calc or Google Sheets) also have the TEXTJOIN() function. If Excel is not an absolute requirement for this task/project, open the spreadsheet in one of these other spreadsheets and use the TEXTJOIN() function.
If Excel is an absolute requirement, and you are allowed to use VBA, one could write a VBA UDF that mimics the TEXTJOIN() function.
I would probably use one of those strategies for this. Will any of those strategies work for you?
Originally Posted by shg
A simple and fast way to do this is to put this formula in cell B1:
=A1
and this in B2:
=B1 & " " & A2
Copy this formula down as far as you need to - the last cell will contain the fully-concatenated result that you require.
Hope this helps.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks