I'm trying to modernize a Wordpad inventory list to Excel 2013 with a problem.
Item description & quantity are now pasted into the same Excel cell.
It's written with item description followed by inventory account in ( ) Since the inventory is inaccurate we need to just delete all ( ) and numbers inside which vary from 1-6 digits.
I tried Find & Replace with wildcards. I also tried the =Left and =Right functions after reading about them.
It looks like this
2" Caps (98)
3" Caps (1257)
4" Caps (188)
6" Caps (89)
5' Wire (12500)
It should look like this
2" Caps
3" Caps
4" Caps
6" Caps
5' Wire
If you have the data in column A and put that formula into B1, then copy and paste it into the rest of the cells in column B, then you will end up with the text in column B minus the (xxx). With your example data it will look like this:
Home built i7-8700K, Hp Envy x360 EVO Laptop, MS Surface Pro 7
CPU
3.7Ghz Core i7-8700K, 11th Gen Core i7-1165G7 4.7Ghz, 10th Gen Core™ i5-1035G4 1.1Ghz
Motherboard
ASUS TUF Z370-Pro Gaming, HP, MS
Memory
16G, 8G, 8G
Graphics Card(s)
AMD Radeon RX580, Intel Iris X Graphics, Intel Iris Plus Graphics G4
Sound Card
ATI High Definition Audio (Built-in to mobo)
Monitor(s) Displays
Dual Samsung U32J59 32 inch monitors, 13.3" display, 12.3" display
Screen Resolution
3840x2160 (Desktop), 1920x1080 (laptop), 2736x1824 Pro 7
Hard Drives
500GB ssd boot drive with 2 & 10TB Data (Desktop), 512GB ssd (laptop), 128GB SSD (tablet)
PSU
Corsair CX 750M
Case
Antec 100
Cooling
Coolermaster CM 212+
Keyboard
IBM Model M - used continuously since 1986
Mouse
Microsoft IntelliMouse
Internet Speed
665Mbps/15Mbps down/up
Browser
FireFox, MS Edge
Antivirus
Defender on all
Other Info
Retired in 2015 after working in the tech industry for 41 years. First 10 years as a Technician, the rest as a programmer/software engineer. After 1 year of retirement, I was bored so went back to work as a Robotic Process Automation Consultant. Retired for 3rd (and final) time in 2019.
Thank you strollin. It worked perfect.
Just one more ? How do I make it work for the whole page at once?
I know about grabbing the handle and pulling it down. Not that I'm lazy but because I want to learn what tools Excel gives me and how to use them.
I tried inserting $ before A1 but that didn't work. (maybe that only works for Conditional formatting) which I learned about last week
Now that you fixed your original post it's what I thought you would have wanted in the first place. The concatenate function is no longer needed and the formula can be shortened to:
=TRIM(LEFT(A1,FIND("(",A1,1)-1))
You can't apply the formula to the whole sheet at once but you could write a VBA macro that would loop thru the rows on column A and do the same thing as the formula above.
Home built i7-8700K, Hp Envy x360 EVO Laptop, MS Surface Pro 7
CPU
3.7Ghz Core i7-8700K, 11th Gen Core i7-1165G7 4.7Ghz, 10th Gen Core™ i5-1035G4 1.1Ghz
Motherboard
ASUS TUF Z370-Pro Gaming, HP, MS
Memory
16G, 8G, 8G
Graphics Card(s)
AMD Radeon RX580, Intel Iris X Graphics, Intel Iris Plus Graphics G4
Sound Card
ATI High Definition Audio (Built-in to mobo)
Monitor(s) Displays
Dual Samsung U32J59 32 inch monitors, 13.3" display, 12.3" display
Screen Resolution
3840x2160 (Desktop), 1920x1080 (laptop), 2736x1824 Pro 7
Hard Drives
500GB ssd boot drive with 2 & 10TB Data (Desktop), 512GB ssd (laptop), 128GB SSD (tablet)
PSU
Corsair CX 750M
Case
Antec 100
Cooling
Coolermaster CM 212+
Keyboard
IBM Model M - used continuously since 1986
Mouse
Microsoft IntelliMouse
Internet Speed
665Mbps/15Mbps down/up
Browser
FireFox, MS Edge
Antivirus
Defender on all
Other Info
Retired in 2015 after working in the tech industry for 41 years. First 10 years as a Technician, the rest as a programmer/software engineer. After 1 year of retirement, I was bored so went back to work as a Robotic Process Automation Consultant. Retired for 3rd (and final) time in 2019.
This was a different solution that works by splitting a whole column where needed. The response is below.
Try the Text to Columns feature in the Data ribbon.
Highlight the column with the data (only 1 column) then click on Text to Columns
Select "Delimited" then Next
Select "Other" and in the entry to the right type in "(" (without quotes)
You should see 2 columns now
Select Next and click in the 2nd column and select "Do Not Import Column"
Click "Next" and you should have what you want
Home built i7-8700K, Hp Envy x360 EVO Laptop, MS Surface Pro 7
CPU
3.7Ghz Core i7-8700K, 11th Gen Core i7-1165G7 4.7Ghz, 10th Gen Core™ i5-1035G4 1.1Ghz
Motherboard
ASUS TUF Z370-Pro Gaming, HP, MS
Memory
16G, 8G, 8G
Graphics Card(s)
AMD Radeon RX580, Intel Iris X Graphics, Intel Iris Plus Graphics G4
Sound Card
ATI High Definition Audio (Built-in to mobo)
Monitor(s) Displays
Dual Samsung U32J59 32 inch monitors, 13.3" display, 12.3" display
Screen Resolution
3840x2160 (Desktop), 1920x1080 (laptop), 2736x1824 Pro 7
Hard Drives
500GB ssd boot drive with 2 & 10TB Data (Desktop), 512GB ssd (laptop), 128GB SSD (tablet)
PSU
Corsair CX 750M
Case
Antec 100
Cooling
Coolermaster CM 212+
Keyboard
IBM Model M - used continuously since 1986
Mouse
Microsoft IntelliMouse
Internet Speed
665Mbps/15Mbps down/up
Browser
FireFox, MS Edge
Antivirus
Defender on all
Other Info
Retired in 2015 after working in the tech industry for 41 years. First 10 years as a Technician, the rest as a programmer/software engineer. After 1 year of retirement, I was bored so went back to work as a Robotic Process Automation Consultant. Retired for 3rd (and final) time in 2019.