Replacing a random string of 5 numbers

K

Katie59

Sorry about this, but I've searched and searched and don't see that HELP
addresses this. I need to remove a dash followed by a sequesnce of 5
numbers. The 5 #'s are different in each cell.
 
K

Katie59

Katie59 said:
Sorry about this, but I've searched and searched and don't see that HELP
addresses this. I need to remove a dash followed by a sequesnce of 5
numbers. The 5 #'s are different in each cell. I need to remove both the dash and the numbers.
Click to expand...
 
B

Biff

Hi!

Try this:

Select the cell(s) in question
Goto Edit>Replace
Find what: -?????
Replace with: nothing, leave this blank
Replace all

Biff
 
R

Ron Rosenfeld

Sorry about this, but I've searched and searched and don't see that HELP
addresses this. I need to remove a dash followed by a sequesnce of 5
numbers. The 5 #'s are different in each cell.
Click to expand...

What's in the rest of the cell? Depending, you may be able to do this with
regular worksheet functions. However, you can easily do this with a regular
expression:

=REGEX.SUBSTITUTE(A1,"-\d{5}")

You'll have to download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr to use this.
--ron
 
S

Sid1962

Why not just select the column, and use Text to Columns. When the window
comes up, make sure Delimited is checked. Then select Other, out in a dash,
and say OK. It creates a new column to the right of the original, with all
of the numbers that were to the right (following) the dash. The dash
disapperas. Then simply delet the newly formed column. I treid it with a
column of 6 or so numbers, and it worked for me.

Good Luck.

Sid1962
 
B

Biff

Then simply delet the newly formed column.

You don't need to do that.

In Step 3 of the T to C Wizard, you can click on the column of numbers and
then select Do not import column (skip).

Biff
 
Top