Removing #'s from text field

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

Thanks,
Dan
 
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
 
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
 
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))
 
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
 
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




.
Click to expand...
 
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
 
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
 
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
 
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
Click to expand...
 
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
 
Back
Top