Removing #'s from text field

[Guest]

Is there a formula that will remove any #'s from a text
field/cell?

 

[Frank Kabel]

Hi
try something like
=SUBSTITUTE(A1,"#","")

 

[Guest]

That didn't seam to work for some reason. Is there another
way, maybe? Would seeing the sample text/cell help?

Thanks,
Dan

 

[JulieD]

Hi Dan

for the moment just describe what the cell contents look like (please don't
attach a workbook) and any details as to how / when / why the # appeared.

Cheers
JulieD

 

[Guest]

Hi Julie, Here's a sample cell, "Overnite Transportation
090401 0914 09012395". I guess what I really want to do
is remove the right most 21 charaters. The field is the
description field in a table of an MS Access General
Ledger database I created & maintain. The #'s represent
different things & are in the report I dowload from the
mainframe & put into Access.

Thanks,
Dan

 

[Frank Kabel]

Hi
so if I understood you correctly you want to delete all characters
after the first numeric value appears?. If this is correct try the
following array formula which has to be entered with CTRL+SHIFT+ENTER
(assumption: A1 stores this data)
=TRIM(LEFT(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:1024")),1)),ROW(
INDIRECT("1:1024"))))-1))

 

[JulieD]

Hi Dan

oh, that makes a bit more sense and explains why Frank's answer didn't work
for you - we thought you literally had "#" in the cell
if all of the numbers are 21 characters (that you want to get rid off), you
can do this say in the cell to the right of these and then after you've
changed them from formulas to numbers (using paste special) you can get rid
of the original column -

=LEFT(A1,len(A1)-21)

(assuming your data is in A1, adjust to suit)

Let us know how you go.

Cheers
JulieD

 

[Guest]

That seams to work. Thanks Frank!

Dan

-----Original Message-----
Hi
so if I understood you correctly you want to delete all characters
after the first numeric value appears?. If this is correct try the
following array formula which has to be entered with CTRL+SHIFT+ENTER
(assumption: A1 stores this data)
=TRIM(LEFT(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(INDIRECT ("1:1024")),1)),ROW(
INDIRECT("1:1024"))))-1))


--
Regards
Frank Kabel
Frankfurt, Germany




.

 

[Frank Kabel]

Hi julie
this will work only if there're always 21 characters to the right which
have to be removed
Wasn't sure either so i came up with the complicated version.
Interested what will work for the OP

 

[JulieD]

Hi Frank

yep - definitely a limitation of my solution ... i think he liked your way -
as do i

How's life in Germany these days?

Cheers
JulieD

 

[Guest]

Hi Julie, there are alway 21 characters there becuase the
field is system generated & your formula works perfect! If
I had a vendor name that ended in a # then Franks would
omit it.

Thank you both for your help!
Dan

 

[JulieD]

Hi Dan

glad it's solved

Cheers
JulieD

Hi Julie, there are alway 21 characters there becuase the
field is system generated & your formula works perfect! If
I had a vendor name that ended in a # then Franks would
omit it.

Thank you both for your help!
Dan

 

[Frank Kabel]

Hi Frank

How's life in Germany these days?

Weather is getting better. So it's time to get the convertible out of
the garage
Have you lived in Germany?

Frank

 

[JulieD]

Hi Frank

yes, a long time ago now i lived for just under a year in Dusseldorf ...also
spent quite a bit of time travelling around - it is one of my favourite
countries.

Cheers
JulieD