trim not working, are spaces spaces?

berlinhammer · Aug 3, 2009

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cjfle%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5Cjfle%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5Cjfle%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman","serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>[FONT=&quot]1 [/FONT][FONT=&quot] [/FONT][FONT=&quot]XS0444499197 2009-642
[/FONT][FONT=&quot] [/FONT][FONT=&quot] <<2009-642.pdf>> [/FONT][FONT=&quot] [/FONT]
[FONT=&quot] 
Hello all, I am having problems removing "2009-642" from the above text string(s) (doesn't matter which)

This is just a sample line and it is not always in the same place so I am afraid a simple mid function from a set starting point will not suffice, the number is also dynamic in size, location and leading digits.

As a result I have created a formula to get rid of the "1 XS0444499197" part of the formula leaving me with " 2009-642 "

Now unfortunately I need to get rid of the spaces (or what look like spaces) just leaving me with the number but infuriatingly I can not get the trim function to work, so I can only guess that it is not in fact reading these spaces as spaces, and I'm at a bit of a loss how to proceed.

would really appreciate some help

many thanks

jon[FONT=&quot]
[/FONT][/FONT]

berlinhammer · Aug 3, 2009

actually having posted that i see how weird the pasted line looked.

put it into notepad first before copying into my spreadsheet seems to be working.

sorry for the erroneous post i think i got it :/

#BoB# · Aug 3, 2009

Have you tried the Clean() function? This takes care of the non-printable characters if there are any.

Richard Schollar · Aug 3, 2009

Hi Jon

You probably have non-breaking spaces rather than actual spaces and so your Trim isn't working. If this is the case, you can use SUBSTITUTE to remove them:

=SUBSTITUTE(A1,CHAR(160),"")

CHAR(160) returns a non-breaking space.

wsjackman · Aug 3, 2009

there are non print characters which neither trim not clean will remove

one way to find out what they are is to put the "word in question in A1
in B1
=code(mid($A$1,(row(a1),1)))
in C1
=char(b1)
copy b1 and c1 and paste down

there will probably be a c the looks like " "
but with an unusual number

try
=substitute(a1,char(xx),"")

xx from the value in column B next to the space

berlinhammer · Aug 3, 2009

thanks for the replies gents

to be honest just ensuring I paste the data in as text seems to work but I shall try some of those codes out of interest to see how they work, been too rushed off my feet to try today

cheers

neb255 · Oct 27, 2015

Thanks, this worked a charm for me.

Richard Schollar said:
Hi Jon

You probably have non-breaking spaces rather than actual spaces and so your Trim isn't working. If this is the case, you can use SUBSTITUTE to remove them:

=SUBSTITUTE(A1,CHAR(160),"")

CHAR(160) returns a non-breaking space.

sifuzain · Nov 21, 2018

Richard Schollar said:
Hi Jon

You probably have non-breaking spaces rather than actual spaces and so your Trim isn't working. If this is the case, you can use SUBSTITUTE to remove them:

=SUBSTITUTE(A1,CHAR(160),"")

CHAR(160) returns a non-breaking space.

Thanks very much for this - replacing CHAR160 with "" worked for me. Trim and clean did nothing. TY!

joeu2004 · Nov 21, 2018

But to behave like TRIM, which does special things with interstitial spaces, use:

=TRIM(SUBSTITUTE(A1, CHAR(160), " "))

Note that we replace the non-breaking space (ASCII 160) with a space, not with the null string (""). To be clear, we could write CHAR(32) instead of " ".

trim not working, are spaces spaces?

berlinhammer

Board Regular

Excel Facts

berlinhammer

Board Regular

#BoB#

Active Member

Richard Schollar

MrExcel MVP

wsjackman

Well-known Member

berlinhammer

Board Regular

neb255

Board Regular

sifuzain

New Member

joeu2004

Banned user

Similar threads

Forum statistics

Share this page

trim not working, are spaces spaces?

Board Regular

Excel Facts

Board Regular

Active Member

MrExcel MVP

Well-known Member

Board Regular

Board Regular

New Member

Banned user

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock