removing character from field

Hi all, new to excel and slowly learning..

Anyway, i have a file with about 13000 rows, and in one text field i need to remove all instances of the character ...

'

(thats an upper apostrophe)

I've tried the find/replace but it keeps coming back with 'formula too long'


Any help would be appreciated.

Also, whilst i'm on :D i need to add a .jpg to a number field

eg.. its currently '12564' and i need it as '12564.jpg' ?

Any help would be great ..

cheers
adi

no-one?

Okay, does anyone know what the 'formula too long' is referring to?

A discussion on this
http://www.ozgrid.com/forum/showthread.php?t=32175

Mangesh

This isn't the Psychic Friends Network and we can't divine what you're asking.

Posting a proper question is essential. See Chip's advice on how to post

http://www.cpearson.com/excel/newposte.htm

"unknown" wrote:

>

okay.. well here you go then.. a sample row of data in CSV. The line breaks are inherent in the text.

ARRET100,ARROW ET100 240V HDUTY BRAD & NAIL GUN,55,TAXABLE GOODS,18316,"ARROW ET100 240V HDUTY BRAD & NAIL GUN

Non-slip cushioned grip and superb balance assures effortless work, even during long jobs. Specially angled channel to handle difficult corner, edging and framing jobs. Provides nail driving muscle without the burden of an air compressor. Shoots 3 different size beads.
Solid state circuitry.500 watts.
Hardened carbon steel delivery system for jam proof performance. Trigger and surface contact safety locks combine to offer increased years of safe, accurate trouble free service.

10 amps of power.
18 gauge beads available as follows:
ARRBN1810 Brad Nails 15mm Brown Head
ARRBN1812B Brad Nails 20mm Brown Head
ARRBN1816B Brad Nails 25mm Brown Head
ARRBN1816N Brad Nails 25mm White Head",Hand Tools,Soldering Riveting and Tacking,Arrow,EOREOR

Basiclally i want to search all of the highlighted text field and remove any instances of '

The file is fairly large with approcx 13000 rows. Oh and i'm using excel 2003. I was simply trying a find/replace for the character ' and replacing with a space.Whereupon i was getting the error 'formula too large'.

No idea of how to solve your Excel problem.

However, if it's a CSV file, just open it in Notepad and do the replace there



"adibranch" wrote:

>
> okay.. well here you go then.. a sample row of data in CSV. The line
> breaks are inherent in the text.
>
> ARRET100,ARROW ET100 240V HDUTY BRAD & NAIL GUN,55,TAXABLE
> GOODS,18316,"ARROW ET100 240V HDUTY BRAD & NAIL GUN
>
> Non-slip cushioned grip and superb balance assures effortless work,
> even during long jobs. Specially angled channel to handle difficult
> corner, edging and framing jobs. Provides nail driving muscle without
> the burden of an air compressor. Shoots 3 different size beads.
> Solid state circuitry.500 watts.
> Hardened carbon steel delivery system for jam proof performance.
> Trigger and surface contact safety locks combine to offer increased
> years of safe, accurate trouble free service.
>
> 10 amps of power.
> 18 gauge beads available as follows:
> ARRBN1810 Brad Nails 15mm Brown Head
> ARRBN1812B Brad Nails 20mm Brown Head
> ARRBN1816B Brad Nails 25mm Brown Head
> ARRBN1816N Brad Nails 25mm White Head",Hand Tools,Soldering Riveting
> and Tacking,Arrow,EOREOR
>
> Basiclally i want to search all of the highlighted text field and
> remove any instances of '
>
> The file is fairly large with approcx 13000 rows. Oh and i'm using
> excel 2003. I was simply trying a find/replace for the character ' and
> replacing with a space.Whereupon i was getting the error 'formula too
> large'.
>
> Any more info you need?
>
>
> --
> adibranch
> ------------------------------------------------------------------------
> adibranch's Profile: http://www.excelforum.com/member.php...o&userid=23670
> View this thread: http://www.excelforum.com/showthread...hreadid=373450
>
>

I tried to simulate your case, and i dont get any error for a find/replace. I copied your e.g. in 13000+ rows for this.
As for your second query, i.e. adding .jpg, in another column just enter this formula:
=A1 & ".jpg"
where A1 is the number column to which you want to add .jpg, and then copy paste-special-values to the original column.

Mangesh

Right, following on from this..

I've discovered its due to the size of the field (i've highlighted in green above, but not that particular text) . All goes fine until about 450 rows down where it hits the field with a lot of text...

BLACK AND DECKER CHAINSAW - 30CM BAR

COMPLETE WITH 12 METRES OF CABLE.



The Black & Decker Chainsaw range can tackle most heavy duty sawing jobs from cutting logs for fires to pruning large tree branches. Packed with practical safety features you don't have to be a lumberjack to enjoy the benefit of these powerful outdoor tools.



Safety features include anti kick back facility which stops the chain within 0.15 seconds in the event of kick back and a safety lock off switch to prevent accidental starting.



Automatic chain oiling improves cutting performance and extends the life of the chain and the chain is tensioned by a convenient built-in tool-free chain tensioner.



NB 12 metres of cable is supplied.



Specification :

Motor Power : 1600watts.

Max. Cutting Length : 30cm/12in.

Chain Speed (No Load) : 8m/sec.

Weight (assembled) : 3.6Kg.

Oil Capacity :180ml.

Chain Break : 0.15 Seconds.

Chain type : Chrome.

Sprocket Nose Type : Yes.

Auto Stop : Yes.

Auto Anti-Kickback : Yes.

Chain Type : A6154.



NOTE. Chain Saws can be dangerous. Always refer to the manufacturers instructions before use. The operator should always wear proper chainsaw gloves for protection in use.



Plus 12 metres of cable.

I'm assuming that this amount of text is too large to check, hence the 'formula too large' and that the only way around this is to split the text into two fields, check both, then recombine? Is there an easier way? I'm currently at about ten macros and i dont really want to add many more... ( i know it can be done in VB, it's just that i'll have to get someone to write it for me).

I recorded this macro for find replace. You could give it a shot.


Sub Macro1()
Worksheets("Sheet1")
Columns("E:E").Select
Selection.Replace What:="'", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


In this macro, replace Sheet1 with the name of your sheet, and the column E:E with your column which has that text

Mangesh

cheers i'll give it a try. I may also have found anothe way round it...

Thanks for the replies all.