Excel 2010 :: How To Modify Text Content Of A Cell

Dec 12, 2013

I want to edit phone numbers in format 1112224444 to 111-222-4444.

My best shot has been...

Excel 2010 (Mac)

Sub EditPhone1()
ActiveCell.Offset(0, 0).Select
With ActiveCell
.Value = .Characters(1, 3) & "-" & .Characters(4, 3) & "-" & .Characters(7, 4)
End With
End Sub

I've tried many variations, no luck.

View 2 Replies


Modify Cell Background Color Based On Cell Content

Jul 1, 2014

I am working on developing QR Codes using some MATLAB code and it would be really convenient if I could create an excel program which changed the background color of a cell containing a 1 to black and a cell containing a 0 to white.

View 2 Replies View Related

Excel 2010 :: Making Hard Coded Cell Dynamic While Maintaining Original Content

May 16, 2014

I pulled a set of financial statements from a database into Excel. All of the values are hard coded but I would like to make them dynamic, while maintaining the original content. For example, If cell C3 is a revenue figure for 2012 ($1,000) I would like to link it to cell C5, which will be a currency conversion rate (2.0). So I would like cell C3 to read =1000*C5, which would equal 2000, and for cell D3 to equal 2013 sales*D5, etc. I would also like this to go across a specified range so that I can also change operating expenses, taxes, etc over a number of columns (dates) that I define, all linking to the conversion rate for the given year. Having another tab where you link and anchor is an option but I would prefer something in VBA where I can qickly include this functionality with new workbooks that I download.

I am using Excel 2010.

View 5 Replies View Related

Excel 2010 :: Modify Values In Short List Of Currencies In Home / Number Toolbar

Jun 27, 2013

I recently moved to Central America and now am working on models that are either in $ or in the local currency (Q). I do not want to change my default currency value in the regional settings of the control panel because it is more convenient for me to stay in $ but it is a pain everytime I want to set a worksheet in Q and not in $.

So, I am wondering how I can change the default values in the currency drop-down list on the Home toolbar under Numbers in Excel 2010.

Currently, it gives me the change to apply the following currencies:

- $ English (US)
- £ English (UK)
- € Euro (€ 123)
- ¥ Chinese (PRC)
- fr. French (Switzerland)

Instead of either the £, ¥ or fr., I would like to have listed the Q, my other frequently used currency.

View 3 Replies View Related

Excel 2010 :: Perform Sum Based On Other Cells Content?

Jan 31, 2013

Exel 2010 cells formatted to Time

need to look at h1 and h2 if no data ignore

other wise add data together if total exceeds 08:45

then deduct 45min from h1

if combined the are less than 8:45 then h3 must equal 8:00 by altering h1 only


h1= 5:00
h2= 2:00
h3= 8:00


h1= 8:00
h2= 2:00
h3= 7:15

View 3 Replies View Related

Excel 2002 :: Modify Formula To Merge Numerical With Text

Apr 21, 2012

I am using the following formula to find out the No. of "HH" in the range given and I am getting the results simply with numerical value as 2, 3 etc. I want the result should be displayed as 2HH, 3HH depending upon the No. of "HH" available in the given range. The result should be displayed as (the No. added with the text "HH").


I use excel 2002.

View 2 Replies View Related

Excel 2010 :: Calculating Value And Posting Total To 1 Of 4 Fields Dependent On Content Of Another

Jan 9, 2014

Excel ( 2010 ). I am creating a Sales Leads spreadsheet. Within the spreadsheet I have a the following relevant fields.

proposed sales value field ( F2 ), Estimated Close - which will be Q1,Q2,Q3 or Q4 ( G2 ), % Probability ( H2 ) & Q1 ( I2 ), Q2 ( J2 ), Q3 ( K2 ) & Q4 ( L2 ).

F2 = 150
G2 = Q2
H2 = 10%

I want to calculate the actual value of sales based on %prob and put the total into the correct field ( I,J,K or L ) based on what field G2 says. So in this case 15 into field J2. I have attached an example.

View 4 Replies View Related

Excel 2010 :: How To Fix Cell Text Format

Dec 17, 2011

i am trying to work on a database that has some errors that i want to correct the issue is that some of the cells have data that is listed last, first instead it needs to be listed into first last.

I can not seperate this into 2 fields as it would break the program this database belongs to.

this database is linked from excell to access i am using excell to update an access database

i use office 2010

View 9 Replies View Related

Excel 2010 :: Locating One Cell Data In Text Of Adjacent Cell?

Jul 1, 2013

I am using Excel 2010.

In my worksheet I have 'Column A' and 'Column B', In 'Column A' are product I.D. numbers. In 'Column B' is a text description of the product, whose I.D. number is in 'Column A,' and should also contain the I.D. number from 'Column A' somewhere in the midst of the descriptive text. However, some of these in 'Column B' do not.

I need to create a function that looks for the value in 'Column A' and determines whether or not it is present in the text of 'Column B'. Therefore, spitting the answer out in 'Column C' so that I can copy it down for 100,000 cells.

View 5 Replies View Related

Excel 2010 :: Replacing Part Of The Text In A Cell

Jun 1, 2014

I'm running Excel 2010.

In a field containing this: Bergamot, Orange*(11,16) I want to put a space before the asterisk. There are hundreds of these in the spreadsheet with other text before and after the text to be replaced. I made sure the field is defined as a text field.

I searched on "*(" and said replaced with " *(" and ended up with " *(11,16)" - the Bergamot, Orange was removed from the field.

View 6 Replies View Related

Excel 2010 :: How To Combine A Formula And Text In Same Cell

Jun 20, 2014

How do I combine a formula and text in the same cell in excel 2010.

What I am looking for is the sum of a number of cells, followed by text.

e.g. something like =SUM(E4:E50)"/35"

If the sum result of cells E4:E50 was 10, I would be expecting to see visible in the cell '10/35'

How do I write this formula?

View 3 Replies View Related

Excel 2010 :: If Cell Contains Red Text In Column Then Delete Row?

Mar 10, 2014

I'm using Excel 2010). I have column I, which is the sum of columns A and B (first and last names of patrons), and I have used conditional formatting to identify all duplicates in column I with red text. I would now like a macro to find red text in column I and then delete the corresponding rows.

I've been looking around on the web for a while and have cobbled together this code stolen from other people's macros:

[Code] ....

When I run it, I get the following error:

Run-time error '1004':Application-defined or object-defined error

Excel doc is called "stupid mailing list.xlsm", sheet I want to run the macro on is called "voters".

View 1 Replies View Related

Excel 2010 :: VBA Macro To Split Text In Every Cell Of The Same Column?

Apr 16, 2014

Excel VBA 2010.

I have this problem:
e.g Cells ""
C2 - 128.50 g
C3 - 211.01 g
C4 - 198.50 g
C5 - 179.34 g

I need to split the text into
Cell "C2" = 128.50 and Cell "D2" = g
Cell "C3" = 211.01 and Cell "D2" = g
and so forth

I wanted to avoid using delimited method with space and induce an automated method to format every cell of Column "C" one by one.

View 6 Replies View Related

Excel 2010 :: Linking Cell Text To Chart Title

Dec 21, 2013

Why the heck every time I enter in to the "edit text" area of the chart title box in a chart in Excel 2010 and type "=B27" (without the quotation marks, and understanding the contents of "B27" has the text I wish to display) does this idiot thing simply display "=B27" (again, without the quotation marks)???

View 4 Replies View Related

Excel 2010 :: How To Separate Text From A Phone Number In One Cell

Jan 11, 2014

I have a 2010 version of MS Excel. I have roughly 10000 cells that I need to separate into two columns from one cell.

Here is an example of one cell "John Smith 888-8888".

View 14 Replies View Related

Excel 2010 :: Building Text File Based On Values Of Cell?

Feb 26, 2014

I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.

View 1 Replies View Related

Excel 2010 :: Return Result For Appearance Of Text String In Any Cell In Row?

Jan 30, 2014

I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.

I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.

View 7 Replies View Related

Excel 2010 :: How To Make Concatenate To Generate A Text String Using Custom Formatted Value Of A Cell

May 2, 2014

I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).


I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.

In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";


As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.

Here is a screenshot of what the original and custom formatted cells look like:


As you can see the custom formatting works and does exactly what I wan

The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).

To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:


Where B1 is the custom formatted cell.

The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:


As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".

I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)

My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?

*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.

View 8 Replies View Related

Combine Cell Content With Text File & Save As Text

Feb 21, 2008

- I have excel file with data I need
- I have fixed txt(html) template that i need to integrate Excel information into
- Final result that I want to achieve is saved .txt(html) file with combination of fixed information (text) and data from excel cells.

I need to writing a VBA code for each of above (integrating text & cells, saving results as text)

View 5 Replies View Related

Excel 2007 :: Option To Have Row Heights Set Automatically Based On Content Of Text In Row

Sep 28, 2012

Is there an option to have row heights set automatically based on the content of the text in the row, and given a specific column width?

I can right click, and set each row height individually.

I can select serveral rows stacked one on top of the other, each with limited text, and change all of those rows at the same time.

But when I select all rows, and try to change row height, the row height option is not there.

View 2 Replies View Related

Excel 2007 :: VBA Code To Delete Rows Based On Text Starting Content

Oct 25, 2013

Using excel 2007. I am interested in writing a VBA code to delete rows based on the text starting content. I would like to delete rows with cells that do NOT start with an "S" or "SA"



I would only like to keep the last 3 lines.

View 3 Replies View Related

Excel 2010 :: Macro For Replacing Text In HTM Document - Text To Change Different Every Time

Mar 18, 2014

I am my excel worksheet (excel 2010) I have one cell that changes every day (number). I want this number to open my htm document and replace the same number in a string in the htm and save/close this.

An example:
My htm document is located at C:/ and named XX.htm

The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx

And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:


View 8 Replies View Related

To Convert Cell Content Into Text Using VBA

Jun 12, 2006

Can some correct my code. I want to convert my cell content to text. for example if i have column B with neumeric values (199234) i want to convert this to text and put only the text value (no formula after conversion) in the same cell.

Below is my code, much appriciated if you can

Sub format()
Dim rows As Integer
Dim temp1, gtxt As String
Set sh = ActiveSheet
Set xrow = Range("a1:b1", Range("F1").End(xlDown))
rows = xrow.rows.Count

For a = 2 To rows
temp1 = Cells(a, "B")
ActiveCell.Formula = "=Text(temp1, 0)"
Cells(a, "B").PasteSpecial.Value

End Sub

View 6 Replies View Related

Search Using Cell Content/Text

Oct 4, 2006

need to do a search based on the contents of a cell, but this cell wil change from minute to minute, so the result of that search will obviously differ each time. So far, the relevant line of macro/VBA code is only searching for what was in the cell at the initial time of recording the macro, for example:

Cells.Find(What:="Allen", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Application.CutCopyMode = False

...searches for "Allen" each time, no matter what the contents of the cell which contained "Allen" initially, is now.

View 3 Replies View Related

Separate Text From Alphanumeric Content From A Cell

Jun 5, 2014

example, if cell A1 contains: 13 Salisbury Street Balwyn, I would want Salisbury Street Balwyn to be put in cell B1.

View 10 Replies View Related

Change Cell Content If Text Is Found In Different Cell?

Nov 29, 2011

I need a macro or formula that can parse a column of data and if it finds a specific string of text ,then it changes another cell on that row to zero. It can have multiple text variables but all return the same value. For Example,

artisan - matte - flat black
artisan - matte brushed gold
small - canvas - flat black

is found in a longer string of text

then it should change another cell on the same row to have a value of 0. Also i should mention that i need it to loop as it will be parsing through potentially thousands of rows.

View 6 Replies View Related

Excel 2007 :: Grabbing Format Of Cell To Use Content?

Jul 11, 2012

I have a file which included monetary amounts in many currencies, which appear in Excel 2007 as:

232.44 EUR
103,796.10 CZK
18,071.08 PHP

I need to use the value and currency code, but unfortunately the currency code is part of the cell format (so I can't use, say =right(3) to grab it.

The cell format is set to: #,##0.00 "EUR" , with each cell set according to the currency within.

Of course I will be going back to the source to see what I can do to get something more useful directly, but is there any way I can grab the currency code so I can use it ?

View 1 Replies View Related

On Cell Selection, In Named Range, Clear Content Or Add Text If Empty

Jul 24, 2009

I wrote some code that toggles bewtweeen the word "Yes" and the function clearcontents.

What it does not do, is if you click on cell A1 and change it's contents and you click on the same cell again it does nothing. You need to click on another cell say A2 before you can go and change A1 again.

My code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("Documents")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Value = "Yes"
End If
Application.EnableEvents = True
On Error Goto 0
End If

End Sub

View 3 Replies View Related

Read Cell Content & ADD Content + Font Properties *SOLVE

Sep 12, 2007

I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell

Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?

View 9 Replies View Related

Excel 2007 :: Input Mask To Format Cell Content

Jan 20, 2014

The cell content is to be 0-7 characters in length.

Alpha characters only, i.e., no numeric.

First character must be uppercase.

Remaining characters may be lowercase or blank.

Is that possible to do in MS Excel 2007?

View 10 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved