Replies

you can try this:

say the data you need to add an = to is in column M. add a column next to it, or anywhere really, and do a concatenate formula:

=concatenate(“=”,M3)

This should give you =Number in your new column (say =56). Then just copy and paste-value (under Paste Special) back into the original column.

If you need to hide the = and it is showing up in the cell, make sure the column M is formatted as a number. Then you can highlight that column and do a find on = and replace with =. This will then “hide” the = (and you’ll just see 56 in the cell, but see =56 in the formula bar).

A screwy way to do it, but it gets the job done.

you can try this:

say the data you need to add an = to is in column M. add a column next to it, or anywhere really, and do a concatenate formula:

=concatenate(“=”,M3)

This should give you =Number in your new column (say =56). Then just copy and paste-value (under Paste Special) back into the original column.

If you need to hide the = and it is showing up in the cell, make sure the column M is formatted as a number. Then you can highlight that column and do a find on = and replace with =. This will then “hide” the = (and you’ll just see 56 in the cell, but see =56 in the formula bar).

A screwy way to do it, but it gets the job done.

Thank you everyone for your responses. I did check out Chip’s page, and found elements that could work, but wanted to come here first to see what you guys thought about the project. If some of you think this is a little difficult then I have my work cut out for me 😉

I’ll check out everything suggested here, but may come back with more questions

Thanks,
Eve

Thank you everyone for your responses. I did check out Chip’s page, and found elements that could work, but wanted to come here first to see what you guys thought about the project. If some of you think this is a little difficult then I have my work cut out for me 😉

I’ll check out everything suggested here, but may come back with more questions

Thanks,
Eve

Sorry, I didn’t think of that. Is there a reason it has to be in a pivot table?

You can insert functions by right-clicking the pivot table, then choosing Functions, then Calculated Field. You write your formula while double-clicking the Fields where you want them inserted. [This is a great way to add percentages if you need to add them to a table.]

For Median, just type in the Median formula (like you would in a cell) only using the field instead of a range, so if you wanted a Median of a bunch of scores and the field name is Scores, then it would look like =Median(Scores).

Hope this helps!

Eve

When creating pivot tables, make sure you uncheck the Save Data with Table Layout in the Options section. If the box is checked, that means it will save your data with EACH pivot table — meaning your spreadsheet was “duplicated” 5 times! When you uncheck the box it will always refer back to your original data and save you precious memory!

Another box I always uncheck is the AutoFormat Table option. If you leave it checked and have multiple pivot tables on one spreadsheet, it will resize the columns to fit the last pivot table you refreshed, which can be a big pain depending on what you’re doing.

Don’t give up on pivot tables — they make life very easy (esp. by saving time) once you get to know them!

Thank you, Rory, however this inserts spaces at odd points in all cap names. For example, in SKW-MBT it will change it to SK W-MB T. To make myself understand and learn this code, I fiddled with it until it came out right. I think it incorporates your code with Legare’s. Here’s what I came up with:

Sub AddSpaces()
Dim rngCell As Range, intPos As Integer, strText As String
Dim strCheck As String, strCheck2 As String
Dim intAsc As Integer, intAsc2 As Integer

For Each rngCell In Selection
strText = rngCell
‘Len counts the number of characters in string
‘intPos will then equal number of characters in string
For intPos = Len(strText) To 2 Step -1
‘Mid returns specified number of characters in string
‘so this will return last character in first loop
strCheck = Mid(strText, intPos, 1)
‘Asc returns the character code
‘”A” = 65, “Z” = 90, “a” = 97, “z” = 122
intAsc = Asc(strCheck)
Select Case Asc(strCheck)
‘if lowercase, then next intPos
Case Is > 96
Case Else
‘if strCheck is uppercase, then strCheck2 will equal character to the right
strCheck2 = Mid(strText, intPos + 1, 1)
‘if last character then will move to next intPos
If strCheck2 “” Then
intAsc2 = Asc(strCheck2)
Select Case Asc(strCheck2)
‘if uppercase then next intPos
Case Is < 97
Case Else
'if lowercase then inserts space
strText = Mid(strText, 1, intPos – 1) & " " _
& Mid(strText, intPos)
End Select
End If
End Select
Next intPos
rngCell = strText
Next rngCell
'Inserts spaces around "-"
Cells.Replace What:="-", Replacement:=" – "
'Inserts spaces around "&"
Cells.Replace What:="&", Replacement:=" & "
'Deletes any double spaces
Cells.Replace What:=" ", Replacement:=" "
End Sub

I did the intAsc so I could watch what was happening in the Locals window — otherwise I couldn't figure out what was going wrong.

Anyway, thank you for your help, everyone, as I feel like I'm finally learning what this "VBA jibberish" means!

Eve

Thanks, everyone! You guys rule

Thanks, Rory — this is very helpful!

As extra credit (ha ha), how would you change it so that it would only put spaces before a cap letter if the letter before it was lower case? This way it wouldn’t put spaces in-between caps (like TRW).

Sorry, I thought of this after.

I’ve done that for the interim, but I’d really like to add the spaces eventually so we don’t have to remove them each time. Plus it’s easier to read for longer company names.

Thanks!

Can you please explain how the mod function is helpful? I’ve seen you guys use it and I just can’t seem to figure it out. Like what’s the logic of it in this instance?

Thanks,
Wanting to Learn More

I was able to figure this out (you forgot to mention the equals sign). Unfortunately it doesn’t work going down as well, however I was able to redo my subtotals so they total on the top instead of the bottom (there’s a check box for that) and then using this on my new data. Piece of cake!

Thanks very much!