Insert space between each charcter

[JohnExcel222]

INSERT A SPACE INTO A TEXT VALUE
Hi there,
I would like to add a space between each charcter, using a formula ( no VBA ).
A1: TREE
B1: T R E E

Thanks

 

[Rick Rothstein]

Re: INSERT A SPACE INTO A TEXT VALUE

I would like to add a space between each character, using a formula ( no VBA ).
A1: TREE
B1: T R E E

You might be able to do this with a formula if you are using Excel 365 (I am not sure because I am not using that version of Excel), but if you are not using that version, I am pretty sure you will not be able to do it with a formula. I am about to go to sleep, so I won't see your response for a few hours, so I am going to give you a VBA UDF (user defined function) solution on the off-chance you will need to use it...

Function Spaced(S As String) As String
  Spaced = Trim(Replace(StrConv(S, vbUnicode), Chr(0), " "))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Spaced just like it was a built-in Excel function. For example,

=Spaced(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Momentman]

If you use Excel 2016 (Office 365) , TEXTJOIN function can easily handle that

 

[Fluff]

@JohnExcel222
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
I have deleted your other post.

 

[James006]

Hi,

As an alternative with regular expressions ...

Function SpaceCaps(Txt As String) As String
' Insert Space Before Capitals
  With CreateObject("VBScript.RegExp")
       .Pattern = "([A-Z]([a-z]+)?)"
       .Global = True
       SpaceCaps = LTrim(.Replace(Txt, " $1"))
  End With
End Function

HTH

 

[Rick Rothstein]

Hi,

As an alternative with regular expressions ...

Function SpaceCaps(Txt As String) As String
' Insert Space Before Capitals
  With CreateObject("VBScript.RegExp")
       .Pattern = "([A-Z]([a-z]+)?)"
       .Global = True
       SpaceCaps = LTrim(.Replace(Txt, " $1"))
  End With
End Function

HTH

Just wondering... do you really like the above solution better than the somewhat simple one-liner that I posted in Message #2?

 

[Rick Rothstein]

If you use Excel 2016 (Office 365) , TEXTJOIN function can easily handle that

I thought Excel 2016 was different than Excel 365. No matter, I have a question for you... does Excel 365 have a function equivalent to VB's Split function (one that could be used to break delimited text around those delimiters into an array of values)?

 

[Momentman]

I thought Excel 2016 was different than Excel 365. No matter, I have a question for you... does Excel 365 have a function equivalent to VB's Split function (one that could be used to break delimited text around those delimiters into an array of values)?

As i understand it, the Office 365 is a subscription that can include for example the Office 2016 suite and should now include Excel/Office 2019 and these versions contain all the new functions but if you get the regular Excel 2016 without the Office 365 subscription, you dont have access to the new functions

As to a formula equivalent for the VBA Split function, none exists in Office 365, but that should be very simple to implement. I am sure you have a one-liner UDF for that

 

[sandy666]

enough check here: Excel functions (alphabetical)

 

[Rick Rothstein]

As to a formula equivalent for the VBA Split function, none exists in Office 365, but that should be very simple to implement. I am sure you have a one-liner UDF for that

I do, of course , but I asked to clarify whether a straight formula solution could be implemented or not.