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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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
 

benyam

New Member
Joined
Sep 12, 2002
Messages
4
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
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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
 

Forum statistics

Threads
1,144,433
Messages
5,724,317
Members
422,543
Latest member
TimB_13

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
Top