Wildcards - Find & Replace

**zero760** · 07-20-2009, 02:42 PM

Can someone remind me how to do a find and replace on the following

090226000/Jones/123 Main Street

All data has numerical 9 places first field
I need to eliminate the /Jones/123 Main Street
so that I just have 090226000

I thought * would work but it's not returning the
correct output

**arthurbr** · 07-20-2009, 02:53 PM

Replacing will turn the string 0##### into #####
Maybe something like =left(a1,9)

**NBVC** · 07-20-2009, 02:53 PM

Did you try /* (ie. slash and star)

**arthurbr** · 07-20-2009, 02:59 PM

Originally Posted by NBVC

Did you try /* (ie. slash and star)

NBVC,
this solution deletes the initial 0 if there is one. Or is there some formatting to compensate?

**Palmetto** · 07-20-2009, 03:03 PM

If the number of characters is not always the same, this should still extract just the numeric portion.
=LEFT(A1,LEN(A1)-(LEN(A1)-FIND("/",A1)+1))

**NBVC** · 07-20-2009, 03:05 PM

The OP could format the result cell with Custom:

000000000

or

pre-format column to Text via Data|Text to Columns and skip to 3rd window and choose Text from column data format area.

**NBVC** · 07-20-2009, 03:06 PM

Originally Posted by Palmetto

If the number of characters is not always the same, this should still extract just the numeric portion.
=LEFT(A1,LEN(A1)-(LEN(A1)-FIND("/",A1)+1))

Why not

=Left(A1,Find("/",A1)-1)

**arthurbr** · 07-20-2009, 03:26 PM

If the number of characters is not always the same, this should still extract just the numeric portion.
=LEFT(A1,LEN(A1)-(LEN(A1)-FIND("/",A1)+1))

Originally Posted by NBVC

Why not

=Left(A1,Find("/",A1)-1)

OP statement :

All data has numerical 9 places first field

Why complicate things ?

=left(a1,9)

**NBVC** · 07-20-2009, 03:43 PM

You are correct... I was basing my response on Palmetto's proposal.

**Palmetto** · 07-20-2009, 03:50 PM

Yeah . . . I agree.
Guess we should always take the OP literally where it's obvious and not clutter the response with alternatives for conditions that may never exist.