+8 votes
by (100 points)

Select a part of Excel Cell (Not Complete Cell) and convert it to Uppercase by running a macro.

I have a piece of code which will convert a selected cell or selected range of cells to Uppercase, but my requirement is, Selection will be a part (Sub String) of a cell and it should be converted to uppercase if I execute the macro.

For example, I have a cell with value "Thanks for Helping"

Now my code is:

Selection.value = UCase(Selection.Value)
end Sub

If user selects the cell and runs the macro, cell will be changed to "THANKS FOR HELPING"

Now my requirement is..

If user selects only "Helping" from the cell and Runs the macro cell should be changed to "Thanks for HELPING"

Is it possible achieve this?

9 Answers

+16 votes
by (100 points)

In short: No, it cant be done.

An extremely ugly hack to kind of fix it is the following:

Get the user to select the text and hit CTRL+C to copy it to clipboard, then run macro.

Pull string from clipboard to a variable (you have to add MSForms Reference)

'Clipboard -> variable S
Dim DataObj As New MSForms.DataObject
Dim S As String
S = DataObj.GetText

remove that from the text in the cell with some kind of diff or regex, convert it to upper and concat it with the cell.

It gets it done, but it is not exactly best practice...

+14 votes
by (100 points)

You can pick part of the string in a cell with MID

Dim var As String
var = Mid(ActiveCell.Text, 1, 10)

Mid(string, start, number of characters you want)


So, the user needs to select how many characters to change:

'Throw up a popup box
numbVar= InputBox(Prompt:="Please input how many characters to turn into UPPER", _
          Title:="Anything goes here", Default:="Enter number here")

' Exit if cancel is pressed
If numbVar= "" Then Exit Sub

then put numbVar instead of 10

+12 votes
by (100 points)

try this:

Private Sub test()

    ' n is the number of letter you want to have to upper case
    n = 2

    Cells(1, 1) = UCase(Left(Cells(1, 1), n)) + Mid(Cells(1, 1), n + 1)

End Sub

UCase(string) = transform a string to upper case

Left(string, int) = take the first int characters of string starting from the left side

Cells(x,y) = returns a Cell in position (x,y)

Mid(string, int) = returns the content of a string starting from the character in position int

+8 votes
by (100 points)

You could array_combine them first ending up with an associative array and then use the approach explained here .

implode(', ', array_map(function ($v, $k) { return $k . '=' . $v; }, $input, array_keys($input)));

+5 votes
by (100 points)

If each item in the titles and post correspond to each other (eg titles[1] and posts[1], titles[2] and posts[2]), you could use a for loop. eg.

for($i = 0; $i < count($titles); $i++) {
  echo $titles[$i];
  echo $posts[$i];
  echo "<br>";


  foreach ($titles as $i => $value ){  
        echo  $value  ." <br>" . $posts[$i] . " <br>";  
+1 vote
by (100 points)

Thanks for responding here. I am getting our MSDN content publishers to update the pages on the API to make this clearer. If you are using the latest developer tools for Office platform you should get both versions of the DLL and when you do add assembly you'll see you can pick between the two versions. By default the app is going to add v16.

0 votes
by (100 points)

The error message means that the RelativeLayout contains a LinearLayout which contains the actual widgets: It would be more efficient to remove the Relative Layout and use only the LinearLayout.

Note that this is a warning and you sometimes you do need a useless layout to get your app to look the way you want to (though I haven't encountered many of these situations).

0 votes
by (100 points)

You can use $type: http://docs.mongodb.org/manual/reference/operator/query/type/

INT is 16, BIGINT is 18

0 votes
by (100 points)

That depends on situation, in your case it should be:

$query = 'INSERT INTO tablename (id) VALUES (4)';

In case that you would want to insert an integer and a string, it should be:

$query = "INSERT INTO tablename (id, name) VALUES (4, 'New York')";

In case that you would want to build query by directly inserting variables into the query, it should be:

$query = "INSERT INTO tablename (id, name) VALUES ($id, '$city')";

  • Building a query like this is prone to SQL injection.

In case that you would want to build query by using for example PDO library , it would be:

$query = 'INSERT INTO tablename (id, name) VALUES (:id, :city)';

Related questions

+11 votes
6 answers
+12 votes
11 answers
+7 votes
12 answers
asked Jan 19 by coder guy (100 points)