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

statbid

New Member
Joined
Mar 28, 2009
Messages
8
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:
[TABLE="width: 588"]
<tbody>[TR]
[TD="width: 588"]
  • 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

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
The 59L doesn't meet your 4 letter requirement.

But here's a UDF:
Code:
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
AB
102 59L Dodge Cummins Air Intakes Filters02 59L +Dodge +Cummins Air +Intakes +Filters
Sheet1
Cell Formulas
RangeFormula
B1=addplus(A1)
 
Upvote 0
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
 
Upvote 0
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...

:confused: 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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top