Good Morning- Can I delete the last line in all 5000 cells

[tryin2makeit2]

Hello,
Thank you for reading my forum post.
I have a excel doc with 5000 item descriptions. Each of the 5000 cell's has a text paragraph in it. All of these cells are in 1 column with 5000 rows. I need to delete the last line of every cell in this whole doc.

Thank you for your help.

Have a nice day.

Rob

[Ron Coderre]

I think we'll need an example of the kind of text you're asking us to deal with. Otherwise, we'll just be guessing at possible text patterns, etc.

Ron

[tryin2makeit2]

Hello Ron,
I have a excel file of 5000 + items in it. In the description column there is a cell for each item. The cell has general text description of the item. See below. I am trying to delete the last line which is the Html link to the Manufacturer. When viewing the cell in my excel file the whole html text sentence is on one single line not split like it shows here. I want to delete this last whole text sentence from all the cells in the excel file. Is it possible?

OPTIONS - SLCDR Slim 24x CD-ROM Drive
SLDVDCDRW Slim CD-RW/DVD-ROM Drive
BBPCMB Steel Mounting Bracket
POWER- Input : 100 ~ 240V AC universal
Output: 19V DC out
SIZE - 5.82"w x 2.79"h x 10.2"d
WT.- 5.61 lbs.
<ahref="http://www.4xem.com/products.php?category=15"target="_blank">www.4xem.com</a>

[Ron Coderre]

Try this:

1)Select the column with the problem text
2)Edit>Replace
Find what: <ahref*
Replace with: (leave this one blank)
Click [Replace All]

Does that handle your problem?

Ron

[tryin2makeit2]

Hello
I made a typing error. The last line looks like this. Reminder, it is all in one line , not split like showing here.
<a href="http://www.4xem.com/products.php?category=15"target="_blank">www.4xem.com</a


thanks

[Ron Coderre]

You just need to slightly change what I posted:

1)Select the column with the problem text
2)Edit>Replace
Find what: <a href*
Replace with: (leave this one blank)
Click [Replace All]

Does that work now?

Ron

[tryin2makeit2]

Hello,
I get a window that pops up and say's "formula too long"

I wonder if there is a formula I could enter in the column next to this column which would erase the last line.??

Robert

[swatsp0p]

I entered your sample into a cell in a test spreadsheet. I pressed CTRL+H and entered <ahref* into the "Find what:" box and left the "Replace with:" box blank. When I clicked Replace All, my test cell had the entire link removed.

Are you sure you followed Ron's instuctions exactly?

[tryin2makeit2]

Hello,
When I try the exact instructions it gives a "formula too long" error, Because the cell contents are to long. When I deleted 80% of the cell text and tried it again it would work. It will work when there is only five of so text sentences but not in my long text cell.

Any suggestions?

[tryin2makeit2]

My cells are about this long. The replace option will not work unless I delete about 80% of it.

MODEL- BBPC1GBLAN VENDOR- 4XEM CORP

FEATURES- VERSA Barebone Ultra Mini PC- Black
VERSA Barebone Ultra Mini PC with (1) Gigabit LAN port.
Intel 865GV Chipset. Supports Intel P4 & Celeron, up to 2GB SDRAM.
Ethernet, Firewire, USB, Serial, Parallel Ports. Supports one slim
optical drive and one 3.5" ATA EIDE Hard Disk.
All the common features of our VERSA Barebone Ultra Mini PC plus
1 Gb LAN Port for applications that require high network
throughput.
Note: Barebone systems include the casing, motherboard, CPU heatsink
and cooling fan, AC/DC Power supply, Power cable, Package of
Screws, Rubber feet, driver disk and manual.
CPU, memory, hard disk and CD drive must be added by customer.
* Fully functional Pentium 4 system up to 3.2Ghz
* On-board 10/100 LAN, 1394, USB 2.0
* Fully support Intel Hyper Threading Technology
* Low noise and low power consumption
* Support standard 3.5" hard disk
* Intel 865GV/ICH 5, supporting 800MHz FSB

-- SPECIFICATIONs ------------------------------------
PROCESSOR - Accepts Intel Pentium 4 & Celeron processors 2.0 Ghz to
3.2 GHz, Supports Hyper Threading Technology, VRM 10.0
standard.
CORE LOGIC - Intel 865GV/ICH 5 (North/South), FSB support up to 800Mhz
MEMORY - Supports Dual-Channel DDR 400/333/266 DIMM up to 2 GB
(2048MB), with 512Mb technology.
STORAGE - CD-ROM: Supports all Standard Slim CD-ROM/DVD-ROM, CD-RW,
DVD-RW Combo drives.
HDD : Supports all 3.5" standard IDE hard drive,
ATA-100/66/33 drives.
PCI IDE - Dual Independent ATA-100/66/33 support, Supports up to 4 IDE
devices on dual channel.
AUDIO - Integrated Software AC 97 Audio CODEC, Realtek 650
VIDEO - Integrated Intel Extreme Graphics 2, Support up to 64MB DVMT
video memory.
NETWORKING - Integrated high speed Ethernet 1000/100/10Mbps LAN
controller.
FIREWIRE - Integrated OHCI 1394a 1.1 compliance, Support up to 400Mb
transfer rate.
CONNECTORS - Back Panel : (2) PS/2 ports for keyboard & mouse
(1) RS232/Serial (COM) port
(1) 15-pin VGA port
(1) 19V DC Power Jack input
(1) PC-104 Plus port
(1) 10/100/1000 Mbps/RJ45 LAN port
(2) USB 2.0 ports
(1) Parallel port
(1) Line-Out jack
Front Panel: (1) Microphone port
(1) IEEE-1394a/FireWire port
(1) 10BaseT/100BaseTX/RJ45 LAN port
CONTROLS - Front Panel: (1) Power-on Button
(1) Reset Button
(1) Power-on and HDD LED
MISCELLANEOUS- Hardware Monitor: System, processor temperature, voltage and
fan speed monitor.
Cooling Fan : Internal thermal controlled CPU only
Auto Thermal FAN Speed Control
Power Management: ACPI 1.0b compliance and OS direct power
management.
Wake-on Event : RTC/USB keyboard/modem/LAN/keyboard/mouse.
ENVIRONMENTAL- Operating Temperature: 10 ~ 35 degrees C
Storage Temperature : -40 ~ 70 degrees C
Storage Humidity : 20% ~ 90% (non-condensing)
OPTIONS - SLCDR Slim 24x CD-ROM Drive
SLDVDCDRW Slim CD-RW/DVD-ROM Drive
BBPCMB Steel Mounting Bracket
POWER- Input : 100 ~ 240V AC universal
Output: 19V DC out
SIZE - 5.82"w x 2.79"h x 10.2"d WT.- 5.61 lbs.
<a href="http://www.4xem.com/products.php?category=15"target="_blank">www.4xem.com</a>

[swatsp0p]

try this in a helper column next to your Description cell (B1?):

=LEFT(B1,FIND("<",B1)) assuming you don't use the 'less than' sign (<) anywhere in your description before the web link.

HTH

[tryin2makeit2]

Hello,
It is raining , snowing and thundering outside in California and you guys back east are going to get it in a few days.
Another foot of snow here last night. The birds dont know what to do.

Anyways.
I get a error when inserting the formula in B1 that says trying to create a circular reference. Nothing shows up.
uhg

[swatsp0p]

I don't know what cell your description is in. In my example, I assumed B1. In a 'helper column' (like C1) enter the formula. Don't put it IN B1.

HTH

[tryin2makeit2]

Yes I had my description cell in A1 and put the formula in B1 and that is why it did not work.
I then put the description in B1 and the formula in C1 and it worked.
Thank you very much. I really appreciate your help.
It has been cold here. I am out of firewood, and the Heater broke. I am just a little slow today.

Thank you. Take care.

[swatsp0p]

Well, I'm glad we got it figured out. I'm in Minnesota...no snow here!!!

Thanks for the feedback.

Cheers

Bruce