Remove numbers from text and add new line to separate two strings based on delimiter in a cell

[devaprakash]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Actual[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]london#123;new york#34;[/TD]
[TD]london;
new york;[/TD]
[/TR]
[TR]
[TD]delhi#145;chennai#54;[/TD]
[TD]delhi;
chennai;[/TD]
[/TR]
[TR]
[TD]hyderabad#748;[/TD]
[TD]hyderabad;[/TD]
[/TR]
[TR]
[TD]Mumbai#456;Pune#234;Kerala#120;[/TD]
[TD]Mumbai;
Pune;
Kerala;[/TD]
[/TR]
</tbody>[/TABLE]


Hello All,

Can you please help me in removing the # and numbers from text and add new line after ; symbol.
So it would save me a lot of time in preparing status.


Thanks in advance.

 

[azumi]

Assumed your data in cell A1 and down:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"0",""),"#","")

and copied down

 

[Rick Rothstein]

Give this macro a try (it assumes your data is in Column A starting at Row 1 and it outputs it results to Column B starting at Row 1)...

Sub ReconfigureData()
  Dim X As Long, Z As Long, Cities As Variant, Temp() As String
  Cities = WorksheetFunction.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)))
  For X = LBound(Cities) To UBound(Cities)
    Temp = Split(Cities(X), ";")
    For Z = 0 To UBound(Temp) - 1
      Temp(Z) = Split(Temp(Z), "#")(0)
    Next
    Cities(X) = Join(Temp, ";" & vbLf)
    Cities(X) = Left(Cities(X), Len(Cities(X)) - 1)
  Next
  Range("B1").Resize(UBound(Cities) - LBound(Cities) + 1) = WorksheetFunction.Transpose(Cities)
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, 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. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ReconfigureData) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. 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.

 

[Rick Rothstein]

Assumed your data in cell A1 and down:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"0",""),"#","")

and copied down

Two points about your formula... first, it will only work in XL2007 or later (too many nested function levels for XL2003 and earlier); and second, it does not provide for the "new line" (for which I assumed the OP meant Line Feed) that the OP asked for... and you cannot simply SUBSTITUTE a ";"&CHAR(10) for each ";" because that would leave a trailing Line Feed character at the end of the text.

 

[István Hirsch]

Select the cells to process then use Find/Replace:

replace "#*;" with "; + ALT(0010)" (without quotes) (in the Replace with box enter a semicolon, then holding down Alt key enter 0010 on the numeric pad). Hope this works for you too.

 

[devaprakash]

Hi Rick

I am receiving type mismatch notification when i run the macro..

 

[devaprakash]

Hi Istvan,

Thanks for your feedback.
I have tried your suggestion, i am able to replace the numbers with ";". in the replace with text field i am unable to enter (0010) pressing alt.

 

[Rick Rothstein]

Hi Rick

I am receiving type mismatch notification when i run the macro..

On what line of code?

 

[devaprakash]

On what line of code?

Runtime Error:13
Type Mismatch

 

[István Hirsch]

Hi devaprakash,

When you hold down (left) Alt and enter 0010 on the numeric pad (!) you do not see any change in the Replace with box (perhaps a blinking point only). In spite of this, „the line feed” is entered there and you can try to perform the replacement.

However, this key combination to produce line feed is often reported to have different effects (for some users) and the cause of the different behaviour often remains unknown.

At me it produced the required result.