extract numeric data from alphanumeric rows using VBA / macros ?

h3lpMe · 10-20-2011, 04:39 AM

Original message posted here:

http://www.excelforum.com/excel-prog...ml#post2623844

Hi folks. I am re-posting my request in a new thread because I came to realize that new questions cannot be posted into a thread already open by another user (violation of forum terms).
=====================================

Hello, I have a similar request regarding a VBA script.

I have lots of data in (all rows belonging to) "Column A" contain alphanumeric text along with spaces, dashes, and commas. I need two pieces of data extracted from each row, and pasted into two other consecutive rows.

The first data piece I need to extract is a dollar amount (with the dollar sign if possible) usually containing pennies to two decimals places.

Example in Cell A7:
Jack owes us $101.24
I would need the amount $101.24 to be extracted/copied to Cell J7.

The second piece I need to extract is an ID number which is made up of either 1 letter and 1 number, or 2 letters.

Example in Cell A9:
James owes us $200, and his section ID# is 9s
I would need the ID# 9s to be extracted to Cell K9.

Example in Cell A11:
Joey owes us $150, and his section ID# is Ks
I would need the ID# Ks to be extracted to Cell K11.

Example picture:

http://oi56.tinypic.com/10f5o2s.jpg

I appreciate any help on this matter Thanks!

**Kyle123** · 10-20-2011, 05:04 AM

Based on the information you've given:

For the ID =Right(A1,2)

For the amount, there is no doubt a formula that will do this faster, but since you have asked for a vba solution:

Please Login or Register  to view this content.

**romperstomper** · 10-20-2011, 06:21 AM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this, then please edit your posts to add links to any and all crossposts in other forums.

h3lpMe · 10-20-2011, 03:38 PM

Originally Posted by romperstomper

Your post does not comply with Rule 8 of our Forum RULES.

Thanks for the reply guys. The VBA solutions look promising. Will post back if I run into any other problems.

Btw, my original thread was accidentally created in this link (as per forum rules):

http://www.excelforum.com/excel-2007...ml#post2623775

**tazrockon** · 10-20-2011, 03:53 PM

Store all the data from the cell in question in a string variable. Then use InStr to find the locations of the $ sign and # sign in the string and use the Left / Mid / Right functions to parse out the actual values.

**romperstomper** · 10-20-2011, 04:10 PM

I did say any and all cross posts in other forums not just posts in this forum. Please add links accordingly.

h3lpMe · 10-20-2011, 04:20 PM

Originally Posted by romperstomper

I did say any and all cross posts in other forums not just posts in this forum. Please add links accordingly.

Wow. You search other forums for duplicates?

Or does ExcelForum own other websites as well? I am not sure the terms of service of other sites will allow cross-posting to their competition, so I don't think I can fulfill your request (at the risk of having my threads, and accounts deleted elsewhere). Sorry.

And what if someone starts two identical threads on two different websites/forums SIMULTANEOUSLY? How is one to go about figuring out which one the original is?

(It's a joke).

**romperstomper** · 10-20-2011, 04:34 PM

Read the rules here and elsewhere - pretty much all forums have similar rules and for similar reasons (read the link in my earlier post)
If you are not willing to abide by the rules on cross posting, or to simply not cross post, I will lock this post.

I and others post in multiple forums - we don't have to search for cross posts.

h3lpMe · 10-20-2011, 05:03 PM

Originally Posted by romperstomper

Read the rules here and elsewhere - pretty much all forums have similar rules and for similar reasons (read the link in my earlier post)
If you are not willing to abide by the rules on cross posting, or to simply not cross post, I will lock this post.

I and others post in multiple forums - we don't have to search for cross posts.

The owner(s) of these forums obviously have nothing better to do than wave their two inch dix over petty issues (issues I have obviously made an effort to rectify more than once). So if you’re going to be an a $s just go ahead, and delete my account, but don’t be sitting behind a computer thinking you can bully people around with your ridiculous rule, after rule, after rule. No wonder Yahoo and Microsoft forums have more users.

Btw, IF YOU DARE spamming me, or selling my email address to any third-party buyer I WILL use legal action against you folks. I have kept copies of all web pages, and emails thus far.

I wonder if you’ll leave this message up for other people to read. Up to the task, sailor?

P.S. - Thank you to everyone ELSE who actually posted a USEFUL reply to my question, and my middle finger to the moderators.

**romperstomper** · 10-20-2011, 05:23 PM

It always impresses me that people will agree to rules and then refuse to follow them - especially the ones that are simple courtesy.
I'm not sure why you think we would want to have any further dealings with you, or why we would want to violate rules just because you do.
I'm impressed you could tell I am a sailor though.
Goodbye.
Oh, and I will leave this up - I don't think it gives quite the impression you think it does.