Search code examples
excelexcel-formulaexcel-2007

How to extract big text from Excel cell in a formatted text with Excel formulas?

I have below text in Excel cell:

$abcd.$efghijk.$lmn.$op.$qrst.

I want above text in following format in Excel cell using Excel formula only:

abcd$abcd.efghijk$efghijk.lmn$lmn.op$op.qrst$qrst.
Solution

  • Here's what I will suggest based on discussion.

    In a general module, insert following code.

    Public Function RepeatCustom(strInput As String) As String
Dim varInput As Variant
Dim i As Long
If Len(strInput) = 0 Then
    RepeatCustom = ""
Else
    varInput = Split(strInput, ".")
    For i = LBound(varInput) To UBound(varInput)
        RepeatCustom = RepeatCustom & " " & Mid(varInput(i), 2, Len(varInput(i))) & varInput(i)
    Next
    RepeatCustom = Replace(Trim(RepeatCustom), " ", ".") & "."
End If
End Function

    And then assuming cell containing original data is A2, you can use above UDF:

    =RepeatCustom(A2)

    Just note that the code is minimum and is based on sample posted.