Want to retain text format

**Baalbek** · 06-11-2009, 06:48 AM

Hi!

I have a list that looks like this:

1. Here I have som formatted text.
2. Here too.
10. And here.
110. As well as here.

The content of each line is in a single cell.

How do I go about automatically chopping off the number, the dot and the space before the text, but retaining all formattings?

Hope somebody can help!

Cheers,
Baalbek

**tek9step** · 06-11-2009, 07:02 AM

Can you attach an example !!!

Cheers !!

Tek9Step

**Baalbek** · 06-11-2009, 07:36 AM

Sure!

Here's an example file, the problem is described in this threads first post!

Baalbek

**Baalbek** · 06-12-2009, 01:10 AM

So nobody's had any luck with this then...?

Me neither, yet, and I'm actually starting to think this is something you can't do in Excel.

But then again, a macro that:
1. went into text editing mode
2. disregarded the actual content of the cell
3. deleted from the first character until the first dot
4. and then deleted one more character
doesn't sound impossible... but I just can't manage it.

The reason the second point is there is, I tried to automatically record a macro like this, and ended up with a long list of only the items identical to the first item in the list...

Yep,
Baalbek

**Baalbek** · 06-12-2009, 03:14 AM

Yes, using substitute and find was a good idea, but the other part of the problem remains; the formattings (bold, italics, underlined etc.)

That seems to be some kind of basic issue in excel, which is best highlighted by the example file formatproblem2.xls

You'll see that as soon as excel picks up text from another cell, it drops all formattings. Do you guys happen to know of a way to keep them in the new cell as well?

Baalbek

**Baalbek** · 06-12-2009, 03:16 AM

And the file!

**DonkeyOte** · 06-12-2009, 03:34 AM

I don't know of any way to keep formatting in XL I'm afraid.

My suggestion based on your sample...

Copy data into Word
Run an Edit -> Replace:
Replacing: *.[space] (space being you hit space)
With: [nothing]

In options check "use wildcard" if not selected by default.

Copy results back to XL.

This may/may not work pending your structure of narratives... ie if you have full stops followed by space within the descriptions... in the sample this pattern only occurs after the number ID.

**Baalbek** · 06-12-2009, 04:51 AM

The "replace in word" worked pretty good, but couldn't use "^p" with wildcard checked, which resulted in it deleting everything before each dot, which only left me with the last item in the list!

But still, the formats are just as important, and seem even to be the bigger problem.

Baalbek

**DonkeyOte** · 06-12-2009, 04:56 AM

I'm not sure I can add anything - based on your original sample file the Word approach works, if you real data is different such that the Word approach outlined does not work we'd need to first see it before we could comment on it.

**Baalbek** · 06-12-2009, 07:35 AM

You're right, Here's a file with reliable examples. Just like the last one, but I actually do have full stops followed by spaces in my material (now included). Quite a lot of them. The numbering in my real material just goes to 250, but I have loads of these files, and would really appreciate it if we could find an automatic function to solve this.

Baalbek