number remove, data get

gsrikanth · #1 02-17-2012, 10:49 PM

in the excel
if i remove numbers like 0 1 2 upto 9
i have data

at a time whole numbers
how they remove
for each file iam replace zero with nospace, one with nospace i have many time

JBeaucaire · #2 02-18-2012, 01:03 PM

Please provide 3-4 examples of BEFORE and AFTER so we can see what you want.

gsrikanth · #3 02-22-2012, 03:58 AM

Quote:

Originally Posted by JBeaucaire

Please provide 3-4 examples of BEFORE and AFTER so we can see what you want.

before
james( 33 )
( 71 )
stalin
mike( 17 )
( 37 )
1.5johnson
thompson( 37 )
( 34 )
1.43rosy
denis( 17 )

after
james
-
stalin
mike
-
johnson
thompson
-
rosy
denis

answer

JBeaucaire · #4 02-22-2012, 05:17 PM

I would use VBA for this, not a formula. Do you use VBA?

gsrikanth · #5 02-22-2012, 10:08 PM

Quote:

Originally Posted by JBeaucaire

I would use VBA for this, not a formula. Do you use VBA?

yes for VBA

JBeaucaire · #6 02-23-2012, 05:42 AM

Put this new FUNCTION into a standard code module:

Code:

Option Explicit

Function CleanText(ByVal Txt As String) As String
Dim X As Long     'Code base by Rick Rothstein (MVP - Excel)
    For X = 1 To Len(Txt)
        If Mid(Txt, X, 1) Like "*[!A-Za-z]*" Then Mid(Txt, X, 1) = Chr(1) ' Leave only letters
    Next
    CleanText = Replace(Txt, Chr(1), "")
End Function

Now, use this as a formula. Your strings are in column A, in B1 enter:

=CLEANTEXT(A1)

...and copy down.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Edit Data Source- Linking template charts to new data	lbf	PowerPoint	0	10-28-2011 12:19 PM
Mailing: how to make the "page number" in Word is the same as "row number" in excel w	Jamal NUMAN	Word	1	09-03-2011 11:37 AM
How to remove "Table of Figures" as we do to remove the "Table of Contents"?	Jamal NUMAN	Word	1	07-08-2011 05:40 PM
Powerpoint: adding data to trend lines w/o data labels	HaiLe	PowerPoint	0	04-11-2011 09:21 AM
How to organize and insert blank cells between a huge number of data?	tareq	Excel	12	09-29-2010 02:12 PM