THE BORE
General => The Superdeep Borehole => Topic started by: 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?
-
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?
-
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
-
last time i give advice to you gillo
-
Thanks dudes. Tank u Cajole. Groo just had a better answer for distinguished mentally-challenged fellows than you did.
-
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.
-
Man the one time someone asks a question about spreadsheets and you guys go and answer it before I even see the thread. :gloomy
-
Yeah, this sucks, give the finance nerds some chance to shine.
-
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
-
you should
oh wait, not going to bother
-
you should
oh wait, not going to bother
It's like 7000 records. Hellllppllplplplpl
-
Can't you just change the properties of that column to text?
-
Can't you just change the properties of that column to text?
For some reason, no.
-
new column
="'" & [Cell Reference Here]
Autofill it down, copy, paste special -> values
-
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.
-
I'd ask for any money that you payed that girl back. She obviously can't follow directions.
-
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.
-
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.
-
Basically the problem is that the column isn't being FORCED to be read as text
-
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)
-
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.
-
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
-
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
-
Swap the semi-colon with a comma. Excel may have changed the semi-colon to a colon, too. Needs to be a comma.
-
Yeah, it's probably an international standards thing, as it works fine in my Dutch Excel.
-
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
-
Can you share a piece of it, so I can mess around with it?
-
Yeah, I'll PM you the file. It's all public info
-
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.
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.
-
HA DUYYYYY.
Data>Text to Columns.
Fuuuu. So easy.
Thank you all for the help!
-
Sure, take the easy way out.
-
wat
-
HA DUYYYYY.
Data>Text to Columns.
Fuuuu. So easy.
Thank you all for the help!
That doesn't even make sense, explain?
-
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.
-
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.
-
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