Formula to Append "+" to all words with at least 4 characters in a cell.

[statbid]

Hi everyone, it's been many years since I've posted anything to Mr. Excel and I was glad to see that the community is alive and well after all these year. I've searched through the forum and didn't find an thread that already answered this questions so I thought I would post it and see if there is a solution.

I am looking for a formula that will help me add the "+" character to all words in a cell that are at least 4 letters.

So, if this was the original data:

02 59L Dodge Cummins Air Intakes Filters After I run the formula the result would look like: 02 +59L +Dodge +Cummins Air +Intakes +Filters I'd love any help you are able to offer. Thank you

<tbody>
</tbody>

 

[Scott Huish]

The 59L doesn't meet your 4 letter requirement.

But here's a UDF:

Function AddPlus(r As String) As String
Dim t, x As Long
t = Split(r)
For x = 0 To UBound(t)
    If Len(t(x)) > 3 Then t(x) = "+" & t(x)
Next
AddPlus = Join(t)
End Function

Excel 2010
	A	B
1	02 59L Dodge Cummins Air Intakes Filters	02 59L +Dodge +Cummins Air +Intakes +Filters
Sheet1

Cell Formulas
Range	Formula
B1	=addplus(A1)

 

[statbid]

Scott, that's amazing. I've never worked with UDFs before as I've always been intimidated by VBA. Those are pretty slick and this worked perfectly. Sorry my requirements were a little sloppy. How would you modify this to add a "+" to anything great than 3 characters rather than letters?

Thank you,

Shilo

 

[Rick Rothstein]

Sorry my requirements were a little sloppy. How would you modify this to add a "+" to anything great than 3 characters rather than letters?

Scott appears to be away for awhile, so I thought I would jump in...

As far as I can tell, the code Scott posted already does that. If you are seeing something different, can you post the text that does not work correctly in the UDF?

 

[Scott Huish]

It wasn't the letters specifically that I was referring to, you had a + in front of 59L in your sample which is only 3 characters.

 

[statbid]

Thanks guys. Again, my mistake and sorry for cluttering up this post. It works perfectly. Always amazed at the strength of this community. Thank you Scott and Rick.