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

(Excel 2003).
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

hi Paul,

with vba ?

--
isabelle


Le 2011-12-10 08:18, Paul Hyett a écrit :
> Is it possible to concatenate only the cells in a range which contain text, not numbers?
>
> (Excel 2003).

On Sat, 10 Dec 2011 at 08:25:26, isabelle <(E-Mail Removed)> wrote in
microsoft.public.excel :

>hi Paul,
>
>with vba ?

Preferably not, as I don't know how to use that.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

> > with vba ?
>
> Preferably not, as I don't know how to use that.

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)

On Sat, 10 Dec 2011 at 10:21:49, Rick Rothstein
<(E-Mail Removed)> wrote in microsoft.public.excel :

>> > with vba ?
>>
>> Preferably not, as I don't know how to use that.
>
>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)

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.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

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

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)

On Sat, 10 Dec 2011 at 12:51:08, Rick Rothstein
<(E-Mail Removed)> wrote in microsoft.public.excel :

>> Well it almost works - but if there's a number with a minus sign
>> in front of it, it mistakes that for text.
>
>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
>
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?
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

> 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?

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)

On Sun, 11 Dec 2011 at 04:35:22, Rick Rothstein
<(E-Mail Removed)> wrote in microsoft.public.excel :

>> 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?
>
>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.

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!
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)