Help

benyam

New Member
Joined
Sep 12, 2002
Messages
4
I have a column of data that looks like this

A
123 hello
fffffffff
1234:gggggg
12322222222

And i want get ride of the numbers for the ones that have numbers followed by colon i have tried the find formula but for some reason i couldnt get it to work. Anybody knows how to do this?
Please help, i have go zellions of them to edit

thanks
This message was edited by benyam on 2002-10-04 15:08
This message was edited by benyam on 2002-10-04 15:14
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
is this the only time that a colon appears in your column, when there are numbers followed by a colon? if this is the case, then give this a try:

select your range
press ctrl+H
in the replace with box, type *:*
press 'replace all'

this will replace every instance of a cell that contains a colon with a blank.

HTH
kevin
 
Upvote 0
Hi kskinne and lenze

The problem is i only want get ride of the number and colon in those cells not the whole cell. so if i have 1234:fff then i want get ride of 1234: but keep fff in the cell
 
Upvote 0
actually, if you want to use autofilter to just hide the columns containing a ':' instead of removing them, you would need to use autofilter custom option, does not contain :

kevin
 
Upvote 0
Auto Filter for the semicolon, then
use Text to Column with : as the deliminator. Then copy the text back over the original. Delete the number column
 
Upvote 0
Hi

In say C13 input this formula
=RIGHT(A13,LEN(A13)-FIND(":",A13,1))

I assume that you have in cell A13
1234:gggggg

Should return gggggg

HTH

Jack
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top