Forum Settings
       
Reply To Thread

Excel macroFollow

#1 Dec 13 2012 at 4:47 PM Rating: Good
*
88 posts
I need to make an excel macro for work, and I have absolutely no experience doing so. It's probably a simple macro, but it's still outside my capabilities. I need to format all of the cells in column A to the same style.

An example of the data: "%B6280070001598441^Random Name^4912120?;6280070001598441=4912120?"
Obviously the "Random Name" would be a name and the numbers change throught all of the cells. I need to delete the latter half, "4912120?;6280070001598441=4912120?" and move the name to Cell B. So Cell A would have "%B6280070001598441" and Cell B would have the name.

I have gotten a macro that I made to work, but it only formats cell A1. I changed the range to include of all the cells, but it still only does the first cell.

Thanks for any help!
____________________________
Internuncio-n. pl. in·ter·nun·ci·os, A messenger or an agent; a go-between.
#2 Dec 14 2012 at 1:56 AM Rating: Excellent
Avatar
******
29,905 posts
You can do it with the TRIM to character with wildcards function, or the LEFT, MID and RIGHT functions if the number of characters is static. For example, you have cell A with what, 48 characters of data you want removed. You want to keep what is on the left, so you would use =LEFT(A1 , 48) if you want the middle, you would use MID(A1, 34, 48) where 34 and 48 are number of characters before and after what you want to keep.

You can get fancy with the trim and left functions and combine them with IF statements to process text before or after a specific character. such as the example below that just kills any text in the cell if it has a # or a * in it.
=IF(OR(LEFT(A2,1)="*",LEFT(A2,1)="#"),MID(A2,2,LEN(A2)),A2)

You can also use MID to a specific character. For example:
=MID(A1,FIND(" ",A1)+1,255)
would nuke up to 255 characters before or after a space in the text. instead of a space you could use
=MID(A1,FIND("^",A1)+1,255)
Those types of functions should allow you to drag populate them throughout your sheet. Keep in mind the more functions you have in a workbook, the slower it loads and runs.
____________________________
Arch Duke Kaolian Drachensborn, lvl 95 Ranger, Unrest Server
Tech support forum | FAQ (Support) | Mobile Zam: http://m.zam.com (Premium only)
Forum Rules
#3 Dec 14 2012 at 7:56 PM Rating: Good
****
7,817 posts
Quick question...how the **** do I get rid of unused cells. Apparently, at some point this spreadsheet I'm working with at work was either set, or defaulted to 65K+ lines, and I only need 1k or so. I looked and online and tried their suggestions, but none of them eliminated the problem. My spreadsheet is about 5 MB, and it gets to be a bit of a pain when I have to email it out weekly.
____________________________
People don't like to be meddled with. We tell them what to do, what to think, don't run, don't walk. We're in their homes and in their heads and we haven't the right. We're meddlesome. ~River Tam

Sedao
#4 Dec 14 2012 at 10:22 PM Rating: Excellent
Avatar
******
29,905 posts
highlight the rows or columns you want to remove, (use Shift + click to select first row or column, scroll to end, and select last if you want) then select the drop down on the "delete" button in office 2010 and hit delete row or delete column respectivly. Screenshots if needed. http://www.addictivetips.com/windows-tips/how-to-delete-remove-blank-rows-columns-in-excel-2010-spreadsheet/
____________________________
Arch Duke Kaolian Drachensborn, lvl 95 Ranger, Unrest Server
Tech support forum | FAQ (Support) | Mobile Zam: http://m.zam.com (Premium only)
Forum Rules
#5 Dec 15 2012 at 1:06 AM Rating: Good
****
7,817 posts
My copy of Excel is a little older than 2010. It can convert some, but not all, of .xlsx file extensions.
____________________________
People don't like to be meddled with. We tell them what to do, what to think, don't run, don't walk. We're in their homes and in their heads and we haven't the right. We're meddlesome. ~River Tam

Sedao
#6 Dec 15 2012 at 2:06 AM Rating: Excellent
Avatar
******
29,905 posts
In that case just select the crap out of the colums or rows by using mouse click on the letters or numbers and shift, and then hit either delete or cut. I forget which.
____________________________
Arch Duke Kaolian Drachensborn, lvl 95 Ranger, Unrest Server
Tech support forum | FAQ (Support) | Mobile Zam: http://m.zam.com (Premium only)
Forum Rules
#7 Dec 15 2012 at 11:49 PM Rating: Good
****
7,817 posts
Tried that, all it does is pretty much nothing. If there were anything in those cells, it would clear them but I would still have a sheet with 65k lines on it. This file used to be 9 megs, and by ******* around with it I got it down to about 5 & 1/2, still needs more taken off. I will however give it another try on Monday.

Edited, Dec 16th 2012 12:50am by Kastigir
____________________________
People don't like to be meddled with. We tell them what to do, what to think, don't run, don't walk. We're in their homes and in their heads and we haven't the right. We're meddlesome. ~River Tam

Sedao
#8 Dec 16 2012 at 2:51 PM Rating: Good
*
88 posts
Thanks a bunch! That looks like it will work, and I'll try it when I go into work next.
____________________________
Internuncio-n. pl. in·ter·nun·ci·os, A messenger or an agent; a go-between.
Reply To Thread

Colors Smileys Quote OriginalQuote Checked Help

 

Recent Visitors: 22 All times are in CST
Anonymous Guests (22)