Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Replace part of a cell (first two digits)

I am wanting to replace part of a cell ( the first two digits) with nothing - blank

eg cell contains 441298871657 and i want it to read 1298871657
I am looking for a function to do this, i have tried using the finf and replace function but if 44 appears somewhere else in the value this will also be replaced.

Thanks Matt


Post your answer or comment

comments powered by Disqus
Is there a way to change part of a cell's content? I have the following data -

7/7/2008 1:25:00 PM
7/7/2008 1:18:00 PM
7/7/2008 6:19:00 PM
7/7/2008 12:34:00 AM

I want to change 7/7/2008 to Jul-07 but still keep the time the way it is.
I can change the way the data is viewed (using Format, Cell, Number) but it
doesn't change the actual data in the cell. If I do a Replace command it
replaces all the data and I lose the time information. I need to change the
date data because if not I cannot get a COUNT IF function to work on it. Any
ideas? Thanks!

Hi there,

I want to write a macro that replaces part of a string EG

Where the word "AUTHORITY" appears to replace it with "AUTH"

When I use the code that the macro recorder generated when I used the replace function on the toolbar it does not replace the word when it is part of a larger string of words. The code that I am using now :


	VB:
	
 
Workbooks("Macro.xls").Activate 
Range("E:E").Select 
Selection.Replace What:="AUTHORITY", Replacement:="A", LookAt:=xlWhole, _ 
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
ReplaceFormat:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The word "AUTHORITY" appears in lots of cells but in a larger string as "CHURCH CHARITY + LOCAL AUTHORITY".
How do I write this code so that it replaces the word for me even when it is part of a larger string.

thanks,

Marcus

Hi
I wondered if any of you fantastic VB coders have a piece of code I can try
for the following: I would like to replace the formatting for part of a
cell, eg, I may have the following text in a cell "Me vs You" and for every
instance of Me I need to change the colour and/or font size, however, using
Find and Replace replaces the formatting for the whole of the cell, not just
the instance of the word I'm trying to change.
Does anyone know how to do this?

Is there any way to automatically copy part of a cell's content to another cell?

Eg. In A1 on sheet1, I have the text "Sony - TV". However, in A1 on sheet2, I want the word "TV" to be replaced with something else, say, "discman". So A2 would say "Sony - discman".

Is there any way to maintain the text "Sony" and have excel automatically change "tv" to "discman"?
Reason is that I have different brands (Panasonic, Sharp etc) so I will just be entering the brand in A1 on sheet1. I want it to be copied onto A1 on sheet 2 but only the 2nd part of the word is changes.

Thanks in advance.

Hi everyone,

I'm trying to use conditional formatting to bold part of a cell if it contains the words "Reverse" or "Reclass"

I found a useful VBA code I am running that is able to grab the Reverse portion, but how would I include "Reclass" as well?

ALSO: Since this is a subroutine, how can I code the VBA code such that it runs this automatically in the background?

Sub Bold_String()
Dim rng As Range
Dim Cell As Range
Dim start_str As Integer
Set rng = Selection
For Each Cell In rng
start_str = InStr(Cell.Value, "Reverse")
If start_str Then
With Cell.Characters(start_str, 7).Font
.Bold = True
.ColorIndex = 10
End With
End If
Next
End Sub

Thank you so much!

Hey everyone,

I am working on a macro to give me a list of subfolders. So far its going good. What I need is to add something that will Cut out part of a cell and place it in a new cell.

as an example I have video folders that are named "Videoname (2008)" What I would like to do is remove the 2008 from that and place it in the adjacent cell to make it a1-Videoname | b1-2008

This is the macro that I am currently using to display my folders and their size


	VB:
	
 Range 
     
    Sheet1.Cells.Clear 
    Set startRange = Sheet1.Range("A1") 
     'Parent Directory - Change this to whichever directory you want to use
    ListFoldersAndInfo "C:Documents and SettingsVideos", startRange 
End Sub 
 
 
Sub ListFoldersAndInfo(foldername As String, Destination As Range) 
    Dim FSO As Object 
    Dim Folder As Object 
    Dim R As Long 
    Dim SubFolder As Object 
    Dim Wks As Worksheet 
     
    Set FSO = CreateObject("Scripting.FileSystemObject") 
     
    Set Folder = FSO.GetFolder(foldername) 
    Destination = Folder.Name 
    Destination.Offset(0, 2) = Folder.Size 
    Set Destination = Destination.Offset(1, 0) 
     
    For Each SubFolder In Folder.SubFolders 
        ListFoldersAndInfo Folder.Path & "" & SubFolder.Name, Destination 
    Next SubFolder 
     
    Set FSO = Nothing 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be appreciated

I have a column that has text that needs to all the same number of characters....with zeros added to the beginning of the different parts of the text.

The text is, for example, 12:5 - 120:15 and needs to be 0012:05 - 0120:15

I can't seem to figure out how to add leading zeros to a part of a cell. Basically the text has to all be ####:## - ####:## I tried using format ...it did not work

Thaks in advance.

Hello,

assume my cells ( e.g. A1:E10) are filled with words ( i.e. one word per one
cell); I want to find specific character groups, so if I am looking for a
group 'eel' and a cells contains 'peeling', I want that "eel" part to get
formatted. How could I go about this? In the next step, I would like to
obtain the character group to search for from a specific cell, like in -
"take the contents of F1 and search for it through A1:E10, if it is found,
format that part of a cell contents". Can anyone help?

Hi

I am trying to extract part of a cell's content, I understand how MID
LEFT etc. work but as far as I know they are all based on counting the
number of characters from a certain position within a cell.

What I am looking for is extracting the content of a cell upto a
specific character type e,g

Cell A2 = "HP DL380 G1-2-685"

I want to extract "HP DL380 G1" from the cell, in otherwords, upto the
the 1st hyphen.

The reason I cannot count the characters is that the data set has
mulitple hardware types of various character lenghts.

Is this possible, and if so how?

Many thanks
Andy

I am trying to get the =IF function to test only part of a cell as the
logical test. I am trying to create an Invoice template for online sales.
I will only be charging sales tax for Texas residents. I need the tax
formula to only calculate tax if the state is Texas. I do not want to
separate the City, State and Zip as they are currently a single cell. For
instance,

F4 Houston, Texas 77003
F27 =SUM(F18:F26)
F28 8.25 [Format->Percentage]
F29 =F27*F28

For F29, I have tried =IF(F4="*Texas*", F27*F28, 0) but got no results. Can
I make the formula work to look for Texas only? Or is there a better way to
go about this?

When I use the superscript option after selecting just a portion of the cell,
the superscript appears during edit mode only. How do I get it so it appears
as superscript on the spreadsheet or when printing? Again, I'm only trying
to superscript part of a cell's contents, not the entire cell.

I am trying to get the =IF function to test only part of a cell as the
logical test. I am trying to create an Invoice template for online sales.
I will only be charging sales tax for Texas residents. I need the tax
formula to only calculate tax if the state is Texas. I do not want to
separate the City, State and Zip as they are currently a single cell. For
instance,

F4 Houston, Texas 77003
F27 =SUM(F18:F26)
F28 8.25 [Format->Percentage]
F29 =F27*F28

For F29, I have tried =IF(F4="*Texas*", F27*F28, 0) but got no results. Can
I make the formula work to look for Texas only? Or is there a better way to
go about this?

I am trying to change the font of only part of a cell. After locating text
in a cell through find, I would like to take part of that cell and change the
font. The text I would like to change is at the same but the start location
is different. I was planning on after using the find fucnntion in VBA to use
the following function to change part of the cells font
With ActiveCell.Characters(Start:=X, Length:=4).Font
The only problem is that I need to figure out how to define X. Any ideas on
how to locate the text "(u" in a cell and record its starting position?
Thanks.

I need to know if there is a way to hide parts of a cell

Thanks in advance

Hi,

I am having difficulty with this If statement.

I want to return the value of cell D if I can match Cell A with "TEXT" and find part of Cell B "*TEXT*".

It looks something like:

=IF(AA33="CHILD",IF(FIND("*partnership*",Q33),D33,0))

How do I match on part of a cell and then lookup a second one to return another value?

Thanks and Kind Regards,

Muchado77

I'm having issues copying part of a cell in VB. I can do a copy but can’t figure out how to modify the string value prior to paste in new cell.

Here's what I'm trying to do -

Workbook1.Sheet1.CellC45 = John Doe

I'd like to copy just the last name to a new cell in a different workbook

Workbook2.Sheet1.CellE2 = Doe

Also have to make sure I'm only getting the last word in the original cell as in John Quincy Adams = Adams

Any help would be greatly appreciated!!

Hello, first time here, so I am not sure exactly what to write, but hear goes.

I have a spreadsheet to report on appointments booked and closed, the data is exported from a CRM to excel and then the excel spreadsheet formats this into various reports. Appointments can be post QA'd and as such need to count for two different people as they will both have the sale recorded against them. To this end I have it all working except for when there are two different people;

So there is one spreadsheet with all the raw data, and then the report which pulls the data with various formulas for each person such as;

=COUNTIFS(Opportunities!$E:$E,"Sasha Hopkins",Opportunities!$D:$D,"Aida Omerovic",Opportunities!$A:$A,"In Progress")

So if the value in Ex is "Sasha Hopkins" and the value in Dx is "Aida Omerovic" then it retuns 1 result and counts up until it gives the total number of appointments for Sasha that were booked by Aida.

This works fine, but sometimes there will be a value in Opportunities!$D:$D that may be;

"Aida Omerovic, Vahe Kassardjian"

In this case I would still want it to count this figure but of course because the strings are different it returns a result of 0 instead of 1 and does not count it. How can I get it to look for "Aida Omerovic" within any part of Opportunities!$D:$D whether it is the whole cell or simply part of the cell? I can then replicate this for each cell in the report and do a replace x with y and it should be easy, I just need to know where to start if possible.

I have had a look at the find and search and they seem related to what I need but I am unsure how to implement them.

Any help would be greatly appreciated!

Hi Guys,
Well I am back on the boards. Joy! No really- I love all the help I get here- but I hate not knowing what I am doing!

Ok- so here are my dilema's.

Numero Uno~
I have a cell that reads like this
217-428-3529 2174283529 Unspecified Error 13:28:28 0
I need this cell to read only the phone number
217-428-3529

That is going to be the easy part- I know. I tried to use the formula =MID(A1,FIND(",",A1)+2,2) but that doesn't work- I keep getting a #Value error. If someone could point me in the right way I would really appreciate it.

My second problem is just as easy I bet- I just don't know where to start. I have two lists- each of them will have phone numbers on them in the same format of 111-111-1111 for example. If there is a number on list (tab) 2- I want that number to be deleted from list 1. Does that make sense? This is of course all in the same workbook.

Ok- well if those are the worst things to happen to me today- I will be thrilled.

Thanks in advance for everyone's responses. You guys are the absolute best!

I am trying to copy only the first part of a postcode in a cell to another cell, however obviously some postcodes have 3 digits in the first half and some have 4 is there a way of doing this?

Many thanks

I need to move part of the contents of a cell (all except the last 2
chracters) to a different cell leaving the original cell as it was

i.e.

'ab1 2cd' needs to be come 'ab1 2'

after the space there is always 3 characters the first being a number
the last two are always letters

before the space the can be upto five alpha/numeric chracters

is there a way to do this please

--
slowhand
Posted from - http://www.officehelp.in

I have two cells:
A1: LL1014N_2007-8_SEM1_N
B1: LL1014C_2007-8_SEM1_C

I would like to compare parts of the cell and display something in C3 to show whether they are the same or not.

I would like to compare the _SEM1_ part and the bit before the first _ but not the last letter - the C or the N.

So if it was:
A1: LL1014N_2007-8_SEM1_N
B1: LL1014C_2007-8_SEM2_C

Cell C3 would flag a warning.

Whilst:
A1: LL1014N_2007-8_SEM3_N
B1: LL1014C_2007-8_SEM2_C
Would be OK.

For this:
A1: LL1015N_2007-8_SEM3_N
B1: LL1014C_2007-8_SEM2_C
C3 would flag a warning.

Does that make sense?

Thanks

I have a cell that reads: Excel / Access

I would like to write a formula that will delete the first part of the cell
and then the cell will read Access only.

Please help.

Thank you.

Paola

I have 2 lists that need to be compared. They have the same info, but
slightly different. For instance, one of them has "Mr. Bob Smith" in one
cell. The other list just has "Bob Smith". I need to compare the 2 columns
and delete or mark the duplicates. So basically I need to compare part of
each cell in the column on the first sheet to a cell in a column in the
second sheet.

Hi there!

I'm working on a macro to automate my project, One thing i need to do now is to replace the numerals in a cell from another cell. For example, in sheet1 C2, i have this numeral 20. I will like this macro to replace sheet3 C2 which contains data such as 11. I want the numeral 11 to be replaced by 20. Hope someone can help with this!

Another thing is how do i call from excel macro to ask notepad to open up a .xml file? -- solved this part!

Thanks a lot!


No luck finding an answer? You could always try Google.