Concatenate question

  • Thread starter Thread starter Paul Hyett
  • Start date Start date
P

Paul Hyett

Is it possible to concatenate only the cells in a range which contain
text, not numbers?

(Excel 2003).
 
with vba ?
Preferably not, as I don't know how to use that.
Click to expand...

Unless your range is somewhat small in size (you did not give us a hint at
what the range is), I don't think you will be able to get away with not
using VBA. Let me give you a VBA solution and very easy instructions on how
to implement it.

This VBA code is called a UDF (user defined function) and will create a
function that you can use on a worksheet just like any of the built-in Excel
functions. Here is how to do it...

(1) Press ALT+F11 from any worksheet... this will take you into the VBA
editor.

(2) Once there, click Insert/Module on its menu bar

(3) Copy/Paste this code into the code window that opened up

Function ConcatTextOnly(Rng As Range, Delimiter As String) As String
Dim C As Range
For Each C In Rng
If C.Value Like "*[!0-9.]*" Or C.Value = String(Len(C.Value), ".") _
And Len(C.Value) > 0 Then ConcatTextOnly = ConcatTextOnly & _
Delimiter & C.Value
Next
ConcatTextOnly = Mid(ConcatTextOnly, 2 + (Len(Delimiter) = 0))
End Function

(4) You are done!

Go back to any worksheet with a mixture of number and text cells on it.
Let's say A1:A20 is such a range and let's say the delimiter you want
between each character is a comma/space, then put this formula in any cell
outside of the range A1:A20....

=ConcatTextOnly(A1:A20,", ")

You should see a comma/space delimited list of the text only cells with that
range. If you do not want a delimiter to appear between the text, use the
empty string ("") for the delimiter. Your delimiter can be one of more
characters as needed.

Rick Rothstein (MVP - Excel)
 
Preferably not, as I don't know how to use that.
Click to expand...

Unless your range is somewhat small in size (you did not give us a hint
at what the range is), I don't think you will be able to get away with
not using VBA. Let me give you a VBA solution and very easy
instructions on how to implement it.

This VBA code is called a UDF (user defined function) and will create a
function that you can use on a worksheet just like any of the built-in
Excel functions. Here is how to do it...

(1) Press ALT+F11 from any worksheet... this will take you into the VBA
editor.

(2) Once there, click Insert/Module on its menu bar

(3) Copy/Paste this code into the code window that opened up

Function ConcatTextOnly(Rng As Range, Delimiter As String) As String
Dim C As Range
For Each C In Rng
If C.Value Like "*[!0-9.]*" Or C.Value = String(Len(C.Value), ".") _
And Len(C.Value) > 0 Then ConcatTextOnly = ConcatTextOnly & _
Delimiter & C.Value
Next
ConcatTextOnly = Mid(ConcatTextOnly, 2 + (Len(Delimiter) = 0))
End Function

(4) You are done!

Go back to any worksheet with a mixture of number and text cells on it.
Let's say A1:A20 is such a range and let's say the delimiter you want
between each character is a comma/space, then put this formula in any
cell outside of the range A1:A20....

=ConcatTextOnly(A1:A20,", ")

You should see a comma/space delimited list of the text only cells with
that range. If you do not want a delimiter to appear between the text,
use the empty string ("") for the delimiter. Your delimiter can be one
of more characters as needed.

Rick Rothstein (MVP - Excel)
Click to expand...

Thanks - I'll have an experiment with it...

Well it almost works - but if there's a number with a minus sign in
front of it, it mistakes that for text.
 
Well it almost works - but if there's a number with a minus sign
in front of it, it mistakes that for text.
Click to expand...

Sorry, I forgot about the possibility of negative numbers. Try this code in
place of what I posted earlier...

Function ConcatTextOnly(Rng As Range, Delimiter As String) As String
Dim C As Range
For Each C In Rng
If Not WorksheetFunction.IsNumber(C) And Len(C) > 0 Then
ConcatTextOnly = ConcatTextOnly & Delimiter & C.Value
End If
Next
ConcatTextOnly = Mid(ConcatTextOnly, 2 + (Len(Delimiter) = 0))
End Function

Rick Rothstein (MVP - Excel)
 
Sorry, I forgot about the possibility of negative numbers. Try this
code in place of what I posted earlier...

Function ConcatTextOnly(Rng As Range, Delimiter As String) As String
Dim C As Range
For Each C In Rng
If Not WorksheetFunction.IsNumber(C) And Len(C) > 0 Then
ConcatTextOnly = ConcatTextOnly & Delimiter & C.Value
End If
Next
ConcatTextOnly = Mid(ConcatTextOnly, 2 + (Len(Delimiter) = 0))
End Function
Click to expand...
Unfortunately I can't tell if this works or not, as I have no how idea
how to edit/replace the original one with it?
 
Unfortunately I can't tell if this works or not, as I have no
how idea how to edit/replace the original one with it?
Click to expand...

Press ALT+F11 to go into the VB editor. Look to the left side of the editor
and find a window area labeled "Project - VBA Project" (you will recognize
it because it has a list of all the sheet names in your workbook). Look to
the bottom of the list (scroll down to the bottom if you have a lot of
worksheets and can't see the actual bottom) and find the item labeled
"Modules" (it has a folder looking icon in front of it). If there is a plus
sign in front of the word "Modules" click it to open the folder up to reveal
its contents which, I am assuming from your inexperience with VB, will be a
single item labeled "Module 1". Double click the "Module 1" item to open up
the code window for it. You should see the code I gave you earlier in it.
Delete it and then copy/paste the latest code I posted. You are done... go
back to the worksheet and hit F9 to recalculate it and the ConcatTextOnly
functions will all update using the new code.

Rick Rothstein (MVP - Excel)
 
Press ALT+F11 to go into the VB editor. Look to the left side of the
editor and find a window area labeled "Project - VBA Project" (you will
recognize it because it has a list of all the sheet names in your
workbook). Look to the bottom of the list (scroll down to the bottom if
you have a lot of worksheets and can't see the actual bottom) and find
the item labeled "Modules" (it has a folder looking icon in front of
it). If there is a plus sign in front of the word "Modules" click it to
open the folder up to reveal its contents which, I am assuming from
your inexperience with VB, will be a single item labeled "Module 1".
Double click the "Module 1" item to open up the code window for it. You
should see the code I gave you earlier in it. Delete it and then
copy/paste the latest code I posted. You are done... go back to the
worksheet and hit F9 to recalculate it and the ConcatTextOnly functions
will all update using the new code.
Click to expand...

Well, my screen didn't look anything like you said (perhaps you're not
using Excel 2003?), but fortunately I managed to get it sorted anyway,
by fiddling around with various things.

Thank you for your help! :)
 
Back
Top