Excel Question

[cw1865]

Maybe somebody here can help me out.

Let's say I have a column of cells that have #'s followed by letters, ie. 1500TEXT

Is there a function that I can pull the numeric portion out of that string? Rather than typing down an entire column?

[weegillis]

Just throwing this one out there:

Could you just save it as CSV and then use PHP to parse it and generate the new column? (Not a good answer, I admit - never did get a handle of macros.)

[Doc]

CW-

Yes, there is. Unfortunately, I haven't played with macros in a long while, and I'd have to tear into the book to find it, but I know it had to do with selecting the characters you want to pull.

For instance, a field consisting of entries like "Z1234ABC" can be sorted by character positions #2 through #5, thus ignoring the Z and the ABC. That's only good if your numeric characters always occupy the same number of spaces, though. There may be a better way, but that's the way I found. If you're dealing with varying spacecount, I don't see how you could make it work.

I found out how to do it on an Excel forum. I'll take a look on my other computer and see if I still have that link.

EDIT: Here's the link to that forum, cw. http://www.excelforum.com

[cw1865]

Thanks, Doc, fortunately my #s ARE in the first four all the time. I was able to google the answer based on your response. The function is MID(cell,1,4) The 1 is the beginning position of the string, the four is the last. So, 1500TEXT returns 1500 But you're right, the #s must be in the same position, so if you have a mix of 123TEXT or 140000TEXT, or TEXT54000, this function won't help. These happen to be product skus which are all in the thousands

[Doc]

Thanks, Doc, fortunately my #s ARE in the first four all the time. I was able to google the answer based on your response. The function is MID(cell,1,4) The 1 is the beginning position of the string, the four is the last. So, 1500TEXT returns 1500 But you're right, the #s must be in the same position, so if you have a mix of 123TEXT or 140000TEXT, or TEXT54000, this function won't help. These happen to be product skus which are all in the thousands


Good deal! Now that I see it, I remember the MID command. The gal that helped me out, actually did it with a macro, but your way is simpler. Glad you found it.

[weegillis]

Glad you found it.
As am I (feel like a heel). Teach me for being such an ass.