Merging text with wildcards into one text line

[Wukku]

Hey Everyone,

I hope you can help, I haven't been able to find an answer to my question elsewhere.

I am looking for a way to combine text with Wildcards or special Characters (%) into one value.

For example I have 10 lines of text:

******1*****EA*P

<tbody>
</tbody>

**A*MT*U****EA*P

<tbody>
</tbody>

**A*MT*U****EA*P

<tbody>
</tbody>

F*************** F*************** F*************** F*AT***U1SXAE**P <tbody> </tbody> <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

*A************** FAA******SXAEA2P FAA*********EB*P And I want to combine them into FAATMT1USXAE2P <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

Is there a way to do that via an excel function?

 

[Losnalgas]

Hello. Not sure what you want can be done in one cell. At least not without some super sized mega function. If it's always the same wildcard and always the same length you may have a better chance and I would direct you to the text function library as you'll want to use a combination of those functions. if different wildcards, it will be very difficult. At least from what I understand. But, there are plenty of MVPs that may be able to help you out.

 

[tonyyy]

If you're comfortable with a vba approach, the following might get you started...

Sub MergeText()
Dim LastRow As Long, i As Long
Dim r As Range, rng As Range
Dim MurgedText As String

MurgedText = "****************"
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For Each r In Range("A1:A" & LastRow)
    For i = 1 To Len(r)
        If Mid(r, i, 1) <> "*" Then
            Mid(MurgedText, i, 1) = Mid(r, i, 1)
        End If
    Next i
Next r
Range("B1") = MurgedText
End Sub

It's assumed that the lines of text are in Column A starting in cell A1; the output will be in cell B1. Only one wildcard (*) is used in this example.

Cheers,

tonyyy

p.s. The result from running the macro is "FAATMT1U1SXAEB2P" - which does not match your result. Please re-check your answer.