Author Topic: I HAVE ANOTHER QUESTION. ONWARDS, REBIAK. ONWARDS FRAGEMEMAMMANAMNA (Read 1498 times)

GilloD · « **on:** February 03, 2009, 10:27:27 AM »

The girl who does our Locations fucked up the 7000+ store entries we have. We have 2 huge problems:

In the "State" column, she left a leading space in SOME entries. So instead of "NJ" it's "NJ " and this freaks out the DB.
Under phone number she has some as (555)-555-5555 when they should ALL be 55555555555.

So two Excel Q's: Can I limit the number of characters in a column to TWO?

For the phones I can probably just do "Find+Replace", right?

CajoleJuice · « **Reply #1 on:** February 03, 2009, 10:31:13 AM »

insert another column, make it reference the column with the state codes

and type in left(whichevercolumn,2)

then you can copy and paste special values back into the original column

for the second problem, can't think of anything off the top of my head. how are they formatted? is it text?

GilloD · « **Reply #2 on:** February 03, 2009, 10:36:02 AM »

Quote from: Groo on February 03, 2009, 10:32:42 AM

If you can do search&replace, why don't you just replace all spaces in the state column with nothing?

....

....

....

Okay. DO I just do Find " " replace with

How do I say find NOTHING and replace with NOTHING. Can I do it via Ascii?

I was actually contemplating writing something in C to chew this up and do it for me THAT IS HOW BAD I AM AT EXCEL

CajoleJuice · « **Reply #3 on:** February 03, 2009, 10:37:03 AM »

last time i give advice to you gillo

GilloD · « **Reply #4 on:** February 03, 2009, 10:57:28 AM »

Thanks dudes. Tank u Cajole. Groo just had a better answer for distinguished mentally-challenged fellows than you did.

Bocsius · « **Reply #5 on:** February 03, 2009, 10:59:25 AM »

a not-so-pretty formula to fix your phone numbers

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ","")

(replace A1 with whatever)

and, of course, to clean up your states is just =Trim(A1)

Edit: Regarding input limits, checkout Data -> Validation on the menu bar.

Fragamemnon · « **Reply #6 on:** February 03, 2009, 11:20:49 AM »

Man the one time someone asks a question about spreadsheets and you guys go and answer it before I even see the thread.

Reb · « **Reply #7 on:** February 03, 2009, 12:53:54 PM »

Yeah, this sucks, give the finance nerds some chance to shine.

GilloD · « **Reply #8 on:** February 03, 2009, 01:43:58 PM »

I have another question:

I need to add a single quote (') character to an entire column, preceeding the data so that it treats it as text.

How do i do this? GO FINANCE MEN

CajoleJuice · « **Reply #9 on:** February 03, 2009, 01:45:23 PM »

you should

oh wait, not going to bother

GilloD · « **Reply #10 on:** February 03, 2009, 01:47:10 PM »

Quote from: CajoleJuice on February 03, 2009, 01:45:23 PM

you should

oh wait, not going to bother

It's like 7000 records. Hellllppllplplplpl

Reb · « **Reply #11 on:** February 03, 2009, 01:48:48 PM »

Can't you just change the properties of that column to text?

GilloD · « **Reply #12 on:** February 03, 2009, 01:52:04 PM »

Quote from: Rebiak on February 03, 2009, 01:48:48 PM

Can't you just change the properties of that column to text?

For some reason, no.

Bocsius · « **Reply #13 on:** February 03, 2009, 01:54:32 PM »

new column

="'" & [Cell Reference Here]

Autofill it down, copy, paste special -> values

Reb · « **Reply #14 on:** February 03, 2009, 01:55:38 PM »

You should be able to, I have a formula here to help you out in a roundabout way, but it's in Dutch.

You can add a column with only ' in it, and then combine the two columns.

Here it is while I look for the translation:
=TEKST.SAMENVOEGEN(A1;B1)

Meh, Bocsius' solution works better.

Brehvolution · « **Reply #15 on:** February 03, 2009, 01:56:06 PM »

I'd ask for any money that you payed that girl back. She obviously can't follow directions.

GilloD · « **Reply #16 on:** February 03, 2009, 01:57:27 PM »

Quote from: Zero Hero on February 03, 2009, 01:56:06 PM

I'd ask for any money that you payed that girl back. She obviously can't follow directions.

She came up with a hilarious number of ways to represent a phone number.

GilloD · « **Reply #17 on:** February 03, 2009, 02:00:30 PM »

Quote from: Bocsius on February 03, 2009, 01:54:32 PM

new column

="'" & [Cell Reference Here]

Autofill it down, copy, paste special -> values

Oh what the fuck. Now it doesn't force it as text, it just adds the ' to the field.

GilloD · « **Reply #18 on:** February 03, 2009, 02:03:07 PM »

Basically the problem is that the column isn't being FORCED to be read as text

Reb · « **Reply #19 on:** February 03, 2009, 02:08:00 PM »

What do you want to do with it, that it's so important that it's seen as text?

Try this:

Make 2 columns, your numbers (B) and a column with only ' in al cells (A) and try this formula:

=CONCATENATE(A1;B1)

Bocsius · « **Reply #20 on:** February 03, 2009, 02:11:49 PM »

It does appear "'" & whatever will put the ' in front, unlike you typing it yourself. Who knew.

Anyway, how many digits are these numbers, if you don't mind me asking?

Could be a situation where you need to not format the column as text, but rather change the format from general to number. In general, 123456789012345678 turns into 1.23457E+18. It won't do that formatted as a number. The problem you will run into, however, is that data entered as a number will lose significant digits after the 15th character. so 123456789012345678 turns into 123456789012345000. If you were trying to have 16 character credit card numbers, for example, you've lost that last digit (and you aren't getting it back). To preserve significance on large numeric values, you have to ignore what I said about general and number, and format it as text before entering the large number.

Anyway, once you've formatted the column or cell as text, getting the numeric value back out of it requires you to use =Value(cell ref) in another non-text-formatted cell.

GilloD · « **Reply #21 on:** February 03, 2009, 02:15:06 PM »

Quote from: Bocsius on February 03, 2009, 02:11:49 PM

It does appear "'" & whatever will put the ' in front, unlike you typing it yourself. Who knew.

Anyway, how many digits are these numbers, if you don't mind me asking?

Could be a situation where you need to not format the column as text, but rather change the format from general to number. In general, 123456789012345678 turns into 1.23457E+18. It won't do that formatted as a number. The problem you will run into, however, is that data entered as a number will lose significant digits after the 15th character. so 123456789012345678 turns into 123456789012345000. If you were trying to have 16 character credit card numbers, for example, you've lost that last digit.

Anyway, once you've formatted the column or cell as text, getting the numeric value back out of it requires you to use =Value(cell ref) in another non-text-formatted cell.

Jeebus you guys know a lot about this.

Anyway, they're all phone numbers, 10 digits. They need that little shaded corner that says "THIS IS TEXT NO MATTER WHAT". I don't know why this is so important except that whoever wrote the import for the database made it so. Just formatting the column as text won't work- Excel ignores it.

I don't ever know that it was a numeric to begin with, all I know is that it MUST be forced as text. Rebiak, I'll try that next

GilloD · « **Reply #22 on:** February 03, 2009, 02:17:40 PM »

Quote from: Rebiak on February 03, 2009, 02:08:00 PM

What do you want to do with it, that it's so important that it's seen as text?

Try this:

Make 2 columns, your numbers (B) and a column with only ' in al cells (A) and try this formula:

=CONCATENATE(A1;B1)

This nearly works- It forces the shaded corner thing, but it's filling the cells with #VALUE! which I guess is bad

Bocsius · « **Reply #23 on:** February 03, 2009, 02:20:00 PM »

Swap the semi-colon with a comma. Excel may have changed the semi-colon to a colon, too. Needs to be a comma.

Reb · « **Reply #24 on:** February 03, 2009, 02:21:32 PM »

Yeah, it's probably an international standards thing, as it works fine in my Dutch Excel.

GilloD · « **Reply #25 on:** February 03, 2009, 02:23:11 PM »

MOTHERFING

It still doesn't Force as Text. I ditched the #VALUE! problem and it's copying the text into the cell, but I can't get that apostrophe to force it

Reb · « **Reply #26 on:** February 03, 2009, 02:23:52 PM »

Can you share a piece of it, so I can mess around with it?

GilloD · « **Reply #27 on:** February 03, 2009, 02:28:05 PM »

Yeah, I'll PM you the file. It's all public info

Bocsius · « **Reply #28 on:** February 03, 2009, 02:33:12 PM »

well, i entered 10000 as a number. changed it to be formatted as text, which it did. No green thing in the corner, which is what you're looking for, right?

Typed 10000 right over it, got the green thing. So maybe you're going to have to re-key all the numbers?

Or let a macro do it for you.

Code: [Select]

Sub ReWriteText()

    Range("A1").Select 'Change starting point here
    Dim bDone As Boolean
    Dim sTemp As String
    bDone = False
    
    
    Do
        sTemp = ActiveCell.Value
        ActiveCell.Value = 0
        ActiveCell.Value = sTemp
        ActiveCell.Offset(1, 0).Range("A1").Select 'do not change A1 here!
        If (ActiveCell.Value = "") Then bDone = True
    Loop Until bDone

End Sub

This assumes it is a continuous column without breaks in data. Once it reaches an empty cell, it will stop. For the macro to work, it actually has to change the data. Simply saying ActiveCell.Value = ActiveCell.Value doesn't force a change, that's why I'm setting it to 0 and then putting the original value back in. The format of the column needs to be text before running the macro.

Works for me, at least.

GilloD · « **Reply #29 on:** February 03, 2009, 02:36:05 PM »

HA DUYYYYY.

Data>Text to Columns.

Fuuuu. So easy.

Thank you all for the help!

Bocsius · « **Reply #30 on:** February 03, 2009, 02:37:08 PM »

Sure, take the easy way out.

CajoleJuice · « **Reply #31 on:** February 03, 2009, 02:37:27 PM »

Reb · « **Reply #32 on:** February 03, 2009, 02:39:29 PM »

Quote from: GilloD on February 03, 2009, 02:36:05 PM

HA DUYYYYY.

Data>Text to Columns.

Fuuuu. So easy.

Thank you all for the help!

That doesn't even make sense, explain?

Bocsius · « **Reply #33 on:** February 03, 2009, 02:42:29 PM »

Text-to-columns takes a cell's or column's data and splits it into columns if it is delimitted (comma, tab, etc.). As it turns out, one item of data is all it requires. Step through the wizard, choose the text format, boom. Done.

Reb · « **Reply #34 on:** February 03, 2009, 02:44:08 PM »

Ah, I always skip past the format step when using text to columns. I'm a sloppy Excel user. :$

That one goes in my bag of Excel tricks.

CajoleJuice · « **Reply #35 on:** February 03, 2009, 02:45:44 PM »

Quote from: Rebiak on February 03, 2009, 02:44:08 PM

Ah, I always skip past the format step when using text to columns. I'm a sloppy Excel user. :$

That one goes in my bag of Excel tricks.

yeah, same here

News:

Author Topic: I HAVE ANOTHER QUESTION. ONWARDS, REBIAK. ONWARDS FRAGEMEMAMMANAMNA (Read 1498 times)