formatting excel data

when you say that you want to indent it with 7 leading spaces, you mean that it will pass from 20 to 27 spaces, or 35 to 42 spaces?

(oh and don't worry, i won't leave you without your answer :))

If i understood what you asked for properly, it should look like that:

Public Sub FormattingData()
    For Each Cel In Sheet1.UsedRange.Rows
        If Cel.Font.Italic = True Then
            If Left(Cel.Value, 20) = Space(20) Or Left(Cel.Value, 35) = Space(35) Then
                Cel.Value = Space(7) + Cel.Value
            End If
        ElseIf Cel.Font.Bold = True Then
            If Left(Cel.Value, 15) = Space(15) Or Left(Cel.Value, 25) = Space(25) Then
                Cel.Value = Space(5) + Cel.Value
            ElseIf Left(Cel.Value, 10) = Space(10) Or Left(Cel.Value, 20) = Space(20) Then
                Cel.Value = Space(3) + Cel.Value
            End If
        End If
    Next
End Sub

Select allOpen in new window

Sub check()

check1a = Trim(Mid(Range("A1").Value, 1, 20))
check1b = Trim(Mid(Range("A1").Value, 1, 35))

check2a = Trim(Mid(Range("A1").Value, 1, 15))
check2b = Trim(Mid(Range("A1").Value, 1, 25))

check3a = Trim(Mid(Range("A1").Value, 1, 10))
check3b = Trim(Mid(Range("A1").Value, 1, 20))

    If (Range("A1").Font.Italic = True) And (check1a = "" Or check1b = "") Then
       Range("A1").Value = "       " & Range("A1").Value
    End If
   
    If (Range("A1").Font.Bold = True) And (check2a = "" Or check2b = "") Then
       Range("A1").Value = "     " & Range("A1").Value
    End If
   
    If (Range("A1").Font.Bold = True) And (check3a = "" Or check3b = "") Then
       Range("A1").Value = "   " & Range("A1").Value
    End If
   
End Sub

This code will add 7, 5 or 3 additionnal spaces at the beginning of your cells depending on their values & font style.

If what you meant is to keep only 7, 5, or 3 spaces at the beginning of the cells, then maybe you could type instead of having:

     Cel.Value = Space(7) + Cel.Value

use:

     Cel.Value = Space(7) + Ltrim(Cel.Value)

It will trim all the spaces at the beginning of the cells, and leave only 7 spaces...

Cdebel,

   I never realized you could do it that way... very nice.

Ahh, an opportunity for E-E exchange and learning, so here goes...

I've never read a book, manual, or web page on E-E etiquette in the context you describe.  My experience is E-E experts contribute on everything they believe they can contribute to, some one-upping the others in a fair comradare to ensure the best solution is provided, and learning experience, exchange occurs.  There is nothing gained by responding to a question for the expert, other than the opportunity to contribute, to learn, and to get points which mean almost nothing beyond the ego boost gleaned from helping someone else.

You can view each E-E expert's qualifications before reading the response if you choose, but then all E-E experts started out with 0 points and 0 certifications - I was there 2 months ago, because I never contributed before then, though I've been a member since 2004.

The solution to this question is fairly trivial and I believe most E-E experts can handle.  In fact, with a bit of thought and guidance, you can do this yourself!

Just write a simple vba routine looping through the used range of column A
  within that loop
     check the font style and leading spaces of each cell that has data
        based on font style and leading spaces, change the value of the cell by trimming it, then padding with # spaces based on your "rules"

That's it!

If you're unsure how to write this code, just ask!

I'm sure by this time, some very good E-E experts have already responded - so, enjoy!

Dave

Dave, not knowing VBA, no I cannot do this myself. That's why I (and many others) sign up for and pay a monthly fee for Experts Exchange, so that we can get some assistance. And that's what I'm doing, 'just ask'-ing. As for the EE etiquette: I have never read a book, manual, etc etc on SQL and ETL and have learned on my own through googling and posting questions on various forums and message boards. IN MY EXPERIENCE on EE, I have had some alleged Experts attempt to answer questions, then when not being able to figure out the answer, just drop off. Again, IN MY EXPERIENCE, it seems that in some cases other experts won't answer a question once it's been answered by someone else, so the question never gets answered and I have to repost. IN MY EXPERIENCE, my question is not 'trivial' as you so condescendingly put it. If you have had other experiences here, good for you! I'm just stating what I've encountered, which is not for you to judge or comment on. If you have nothing constructive to contribute to helping me with the VBA code, could you kindly stay off of this question?

To the other experts: thanks so much. I will test out the proposed solutions shortly.

it seems that you both got an issue together.  I don't know what happened between you but as many people on E-E would say, we have to focus on the question.  I didn't commented when dlmiles posted that "solution" because i knew there was a problem.

I start to get used to dlmiles method (see a post that i answered here: https://www.experts-exchange.com/questions/26851340/Excel-VBA-copying-whole-sheet-from-another-workbook.html?cid=1131&anchorAnswerId=34993072#a34993072).  Yes, it's annoying.

I cannot speak for others, but i prefer to not answer when someone started to give an answer (unless this person is a real d... a.. who lead him to the dump).  It's less comfusing for the askers when he get answered by one person solution.   And when i see that the expert is rorya, brettdj, mathewspatrick, i know the asker is in good hand.

Yes, I definitely have an issue with people posting irrelevance on a question when I am trying to get something work-related accomplished.  

cdebel, I tested your solution ID #34983272. When I run the macro nothing happens. Please see the attached file, this is sample data, but this is how the data comes out of Hyperion. As you can see, it's 'tiered' at different levels depending on whether it's a parent account, child account or a line description. And then it's also tiered at different levels depending on whether it's a Revenue or an Expense.

So I want all of the account starting with 'S' (these are the child accounts) to have a leading space of 7. All of the accounts that start with 'X' and 'V' to have a leading space of 5, and all of the Line Description to have a leading space of 3.
Sample-2.xls

I don't understand how you tried to put my code in your sheet, but it work for sure.

See the file YourSampleWithVBA.  Run the macro in it, and you will get the result in Result.xls.

As you can see, it's all indented properly...
Items starting with S have only 7 spaces at the beginning of the cell,
Items starting with X or V have only 5 spaces at the beginning of the cell, and
Items starting with Line Description have only 3 spaces at the beginning of the cell.
YourSampleWIthVBA.xls
Result.xls

Make sure that the sheet where your data reside is named Sheet1.  Because as you can see on line 2, it's the name of the sheet that we are working on in this sample.  If you want to keep a different name, make sure to change the sheet name in the "For Each"

When I run the macro in my data file, I get the following error message...


Macro-error.bmp

I should probably ignore and move on.  And, I probably should have ignored what I perceived as an insinuation from the OP.  However

Gentlemen - my answer was not irrelevant, as I was responding to what I perceived as an insinuation (which I discussed with other EE'ers whose Id's were in cdebel's "distinguished" list, and they didn't feel my post on this question was out of turn).  My post on cdebel's other question was a mistake and I apologized for that - cdebel - this is "not" my method.  I don't make a habit of doing that and I thought I was correcting an error and, to my chagrin, I was in error (acknowledged in the post).

@erp - thanks for your response, and I appreciate your comments in the original post much more clearly.

There's no need to respond, as I'm just clarifying the record.  If you want to see how I handle 99.9% of questions, feel free to review my questions answered - that's the real record.

Peace,

Dave

Ugh, can people please stop posting stuff on here unless it's for the VBA code and getting this resolved?? It's irrelevant and totally unnecessary.

cdebel - I had put my code on the tab 'this worksheet', ran it, got the error, and then took a look at your workbook. I saw that you put yours on Sheet1, so I changed mine. I am not getting the error, but I run the macro and nothing happens. Nothing at all, no error, but it's still not formatting the data.

Okay, I'm fairly certain this is working now. I just have to set up another macro to copy and paste as values so that your macro will work.

I'm going to play around with this some more, but I'm pretty sure you've solved the problem. Thank you so much!!

alright, glad i helped.  If you still have difficulties about this, just buzz me.

This works beautifully, thanks again!