Removing last lowercase letters / charts question

[JonSnow]

2 questions as per excel file attached below. I have Excel 2007.

1. Any formula to remove all lowercase letters after the last uppercase letter?
2. How do I display x-axis in chart as 'month' for both? (see excel file, you'll know what I mean..)
Apart from having the month column inbetween all columns.

Thanks!

[ragulduy]

for question 1:
=SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1,CHAR(ROW($A$65:$A$90)),REPT(" ",99)),99)),"")
based on your text being in A1.

[JonSnow]

for question 1:
=SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1,CHAR(ROW($A$65:$A$90)),REPT(" ",99)),99)),"")
based on your text being in A1.

Hi yudlugar, I can't figure out how to make it work, it shows blank for me. Can you upload a spreadsheet please?

P.S. I know it's been a while

[JonSnow]

Just noticed you're working on 2003.
Spreadsheet in 2003 attached below.

[XOR LX]

Hi,

Will your names ever contain middle names?

Regards

[Andy Pope]

For the axis labels question you need to include a reference to the range containing the labels.

The first chart has a series formula of

=SERIES(Question1!$D$1,Question1!$C$2:$C$13,Question1!$D$2:$D$13,1)

whereas the closed cases chart has a series formula of

=SERIES(Question1!$E$1,,Question1!$E$2:$E$13,1)

You can use the Select Data dialog to set range reference for Category Labels.

[Ursul]

In response to the first question try in cell C2: =LEFT(B2,SEARCH(" ",B2)+1)

[JonSnow]

Hi,

Will your names ever contain middle names?

Regards

Hi,
Yep, unfortunately they do. They also tend to have names like "Capatos de la M, Jose".
And there is a comma inbetween surname and first name. I updated the attached files above to reflect this.

@Andy, I'll try that asap.

@Ursule, it works perfectly for the simple ones! I just realized there's more complicated ones, now, do you think you can update it somehow?

Thanks for your support, guys, really appreciate it.

[JonSnow]

For the axis labels question you need to include a reference to the range containing the labels.

The first chart has a series formula of

=SERIES(Question1!$D$1,Question1!$C$2:$C$13,Question1!$D$2:$D$13,1)

whereas the closed cases chart has a series formula of

=SERIES(Question1!$E$1,,Question1!$E$2:$E$13,1)

You can use the Select Data dialog to set range reference for Category Labels.

Can't believe I haven't seen that. It works, cheers man!

[Ursul]

Hi JonSnow, 2 things, first you have added some tricky names and secondly you have changed the order of the names plus you have added a comma (",") e,g, Caparros De La M, Fabio. That's changing the rules LOL ... so now let's try =LEFT(A2,SEARCH(",",A2)+2) assuming the name is in cell A1

[JonSnow]

Hi JonSnow, 2 things, first you have added some tricky names and secondly you have changed the order of the names plus you have added a comma (",") e,g, Caparros De La M, Fabio. That's changing the rules LOL ... so now let's try =LEFT(A2,SEARCH(",",A2)+2) assuming the name is in cell A1

Haha, yeah, I'm aware it changed things, I was hoping not too much.

Works like a charm! Mersi frumos

Could you briefly explain to me how your formula works?

[Ursul]

Buna Jon, Nu aveti pentru ce. Deci limba romana da? Actually I'm an aussie/kiwi living in Romania - go figure!

OK more on the LEFT function: LEFT returns the first character or characters in a text string, based on the number of characters you specify. So LEFT(text, [num_chars]) =LEFT(A1,5) means look at the text in cell A1 and count 5 characters from the left. So in your case the formula was looking for the comma because the SEARCH function stipulated that, then I asked it to go 2 characters to the right of the comma which was a space plus the next character which happened to be the first letter of name.

For more on this have a look here http://www.techrepublic.com/article/...ing-functions/

O zi buna

[JonSnow]

Buna Jon, Nu aveti pentru ce. Deci limba romana da? Actually I'm an aussie/kiwi living in Romania - go figure!

OK more on the LEFT function: LEFT returns the first character or characters in a text string, based on the number of characters you specify. So LEFT(text, [num_chars]) =LEFT(A1,5) means look at the text in cell A1 and count 5 characters from the left. So in your case the formula was looking for the comma because the SEARCH function stipulated that, then I asked it to go 2 characters to the right of the comma which was a space plus the next character which happened to be the first letter of name.

For more on this have a look here http://www.techrepublic.com/article/...ing-functions/

O zi buna

Got it now, mersi mult pentru tot!

O zi faina si tie