Look For Character In Cell And Insert New Character - For More Than One

Sep 20, 2009

I have a cell which will contain SER01+SER02+SER03

and what i need it to contain is [SER01]+[SER02]+[SER03]

and shocker is i've got this to work for the first instance but not the other two

code as below... be grateful for your help


Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub

View 9 Replies


ADVERTISEMENT

How To Remove Special Character Without Disturbing Other Character

Nov 27, 2012

To all sifus out there, how can i transfer from these:

NAS517-3-2
-41353913
NAS517-3-5
NAS517-3-4
-42MS27253-2
-43353908
-44357182

To these:

NAS517-3-2
353913
NAS517-3-5
NAS517-3-4
MS27253-2
353908
357182

View 1 Replies View Related

Append All Digits Before Character To All Numbers Between The Same Character

May 18, 2008

I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?

Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.

View 5 Replies View Related

Sort Text By Character Length, Insert Spaces, Merge Cells

Apr 19, 2007

I have multiple columns with many rows of unique text in each. Here is an example of what the 1st few rows of column a and b might look like:

A___________B
hboc______dds
jk________optg
hbv_______pl

FIRST:
I would like to be able to sort the rows by the character length in a column. So, it would look like this (if sorted by 1st column):

A___________B
jk_________optg
hbv________pl
hboc_______dds

Second.........

View 2 Replies View Related

Add Character After 1st Character Of Cells

May 6, 2008

Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.

Original A1:A5 = {452, a-bc, 123, 4-52, abc}
Corrected C1:C5 = {4-52, a-bc, 123, 4-52, a-bc}

I can do this using an intermediate working column, but can this be done all in one formula, or via VBA?

My formulaic solution is thus: .....

View 5 Replies View Related

Macro To Insert "+" Character Before A Number

Aug 25, 2009

I have searched this and other forums for a macro which will insert a "+" (international dialling prefix) character before a phone number in an Excel field with no success. I have tried the "record macro" function without success. Any ideas?

View 10 Replies View Related

Add A Character To Every Cell..

Dec 10, 2009

i have a column that is 1900 long and i need to add a "comma" to the end of everything in each cell in that column. How do i go about this? so at the moment it look like this:

random1
random2
random3
random4

But i need to look like this:

random1,
random2,
random3,
random4,

View 3 Replies View Related

Searching A Cell Value For A Character

Apr 28, 2008

I want to test a cell for the type of value as 678/256 or 345/872/098/987 etc. The common character in each would be the "/". First, I need to check the cell for this, then take the first number and compare it to an integer(if it is <> , or = to). I hope I am clear with my situation.

View 14 Replies View Related

Adding A Character To A Cell

Oct 12, 2009

I have a number in each cell 42189654 and I want to add a period in the 3 place. Example 42.189654. I cannot seem to get the correct formula.

View 5 Replies View Related

Always Keeping Character In Cell?

Jan 31, 2013

I'm using the hidden-row trick to auto-fill cells beneath the hidden rows.

For this to work, every cell that is to be auto-filled in must have a character in every cell above it.

How can I make sure that my unfilled cells always have a character in them? If a user deletes the contents of the cell, I want a single character to stay in the cell (I want a backslash). This way, there will always be either a backslash, or the user-filled data in the cell. It can never be empty.

View 3 Replies View Related

How To Find Character From The Cell

Mar 15, 2014

I have following kind of sting in the cell.

<Abc_desc><to_location>10102535</to_location><from_location>100</from_location><abc_nbr>00000000009882203100</abc_nbr><abc_type>C</abc_type><con_qty>113</con_qty><bcd_nbr>00000000009882203</bcd_nbr>

I want to extract the number between <abc_nbr> and </abc_nbr>. i.e., "00000000009882203100".

The starting number of the string may varies.

Macro should pick the number and paste it into the next cell.

View 14 Replies View Related

Remove Last Character In Cell?

Mar 7, 2012

I have a column and each cell in that column has information that ends with a comma "," I would like to remove that comma.

View 4 Replies View Related

Remove 2nd And 3rd Character In A Cell?

Oct 25, 2013

I would like to take a value in a cell and remove the second and third characters in that cell.

For example: "V0010" would become "V10."

I've found formulas for removing the beginning and ending characters, but not a way to remove from the middle.

View 2 Replies View Related

Pull First Character From One Cell To Another

May 27, 2006

I am using an exported dbf from filemaker pro. This is an established dbf, with over 2,000 sku's in it. Here is what I want to do.

I am using Indata and InDesign to create a catalog. I need for excel to pull the first character from a cell and put it in another cell. i.e.

I have a dbf where I have author last name, and title of book. I need for excel to pull the first character of each of these fields and put them in their own cell/column. This will allow me to use these new columns to make the " headers" for my InData/InDesign doc.

View 4 Replies View Related

Check Last Character In A Cell

May 1, 2007

I am creating a macro that saves a file using the value in X1 as a variable name to save the file. I want to check if the last character in cell X1 is a period ("."), and if it is I want to either remove the period from X1 and continue to save, or tell the user it is not possible to save the name that ends in a period.

View 2 Replies View Related

Ensure Cell Cannot Contain A Certain Character

Oct 18, 2007

I enter for example in a cell : 0625-C0/01
sometime 0 is entered as O a letter and not zero.Is there a way to ensure that only 0 (zero) is allowed in that string using data validation rules.

View 6 Replies View Related

Convert Cell Value After Unique Character

Sep 12, 2013

I have instances such as:
A1: 2' x 3'
A2: 2'6" x 8'
A3: 3' x 1'2"

and would like to convert them to:
B1: 2'-0" x 3'-0"
B2: 2'-6" x 8'-0"
B2: 3'-0" x 1'-2"

I am lost trying to figure out how this is accomplished.

View 3 Replies View Related

Returning Value If A Cell Contains Specific Character?

Feb 25, 2014

Essentially what I'm trying to accomplish is import a file from Quickbooks and determine whether it was a Credit Card, Check or Invoice based on the account number.

For example:

45-12345 should return the value CC in an adjacent cell because it contains the "-" character.
I0123456 should return the value INV in an adjacent cell because it contains the "I" character.
01234567 should return the value ACH in an adjacent cell because it doesn't contain either character.

I tried using VLookup, but I can't figure out how to write a formula for when it just contains a character, only if it matches it or is text/number.

View 4 Replies View Related

Search From Cell And Replace With New Character

May 14, 2014

I have a requirement... which need to search from a cel if it march then replace with below name. When user gives a selection screen, month displays in a cell A1.

For example: If this cel (A1) is jan then it should replace with Jan, if mar then replace with mar, if MAI then replace with MAY, if sep then replace with SEP, if OKT then replace with OCT.... and so on...
or
It search the cell A1 for MAI, then replace with MAY & also should search for if A1 is OKT then replace OCT, & also if A1 is DZC then replace with DEC.

I know the function of ....

[=IF(ISNUMBER(SEARCH("*MAI*",A1)),"MAY",A1)] =

This formula brings me only 1 search, but I need 3 search if match replace with respective character. this should search for OKT & DZC ALSO....

present - new

JAN - JAN
FEB - FEB
MAR - MAR
APR - APR
MAI - MAY
JUN - JUN
JUL - JUL
AUG - AUG
SEP - SEP
OKT - OCT
NOV - NOV
DZC - DEC

View 4 Replies View Related

Referencing Certain Character Places In A Cell

May 19, 2009

I'm trying to speed up some work on an excel sheet. The sheet converts degrees latitude and longitude to Northing and Easting coordinates, but right now we have to type them in directly.

For example, the degrees would be 81.0056 and I want to reference just the .0056 from the cell that contains the whole 81.0056 degrees. Is there any way to just reference character place three through seven of that cell? All I'll need it to do is copy it over, the rest of the program will convert it for me.

View 9 Replies View Related

Remove Last Character In A Cell If Its A Comma

Sep 2, 2009

I need to remove the last character in a cell if it is a comma. I can't remove all commas because there are other commas in the text.

View 2 Replies View Related

Search And Replace Character With Cell Value

Mar 24, 2014

A1 value: HARD COVER + SCREEN PROTECTOR - IPHONE 5
B1 value: WH

Want to search for the character "-" and replace it with "WH" from B1 Cell

Do this for all $A column

View 2 Replies View Related

Macro To Add Character To Selected Cell?

Jun 3, 2014

I am trying to create a button that when clicked it will add a character to the existing cell. Eg if cell had LDs and I click button I like it show LDs₂

The ₂ is character code 2082.

View 5 Replies View Related

Using Script To Delete First Character Of Cell

Mar 6, 2014

The script below is used to delete the first character of every cell. I want the commas ',' in the cells starting with a comma to be deleted.

However, it ends up that even for the cells not starting with a comma, the first character is deleted.

[Code] .......

View 2 Replies View Related

Macro To Delete One Character In A Cell

Oct 2, 2007

I'm trying to use Excel 2003 macros for the first time and am very frustrated by it's recorder function. I've used several standalone TSR macro recorders several years ago and they were much easier but certainly not as feature rich.

I have a spreadsheet that I've been using for a couple of years where I have manually entered data. My company has recently started to dump data into an Excel sheet but instead of pure numeric or date values being used they are extracting the values with the ' character starting the value. I want to strip this ' character.

What I do is select the cell I want to edit and invoke the macro. Then press {F2} to edit the contents of the cell, {Home} to move to the left, {Del} to delete the first character - the apostrophe, and {Enter} to complete the edit and move down one cell.

Using the recorder captures the following. Unfortunately it doesn't simply delete the first character, it copies the value of the previous cell to the next cell when I invoke the macro again.

View 10 Replies View Related

Prevent A Character From Been Entered Into A Cell

Oct 15, 2007

Is there any neat little way of preventing a character say '=' from been entered in a cell, kinda similar to the validation but for a 'dont allow' function.

View 14 Replies View Related

Finding/counting A Given Character Within A Cell

Jun 6, 2006

For a given cell, I need to count the number of times a given character (e.g., the letter "a") appears. Is there a worksheet function or formula?

View 9 Replies View Related

Find Specific Character In A Cell

Dec 9, 2009

I am trying to put an IF statement together. If the 7th character from the left is 5 it must show M, and If 0(zero), it must show F

View 2 Replies View Related

Find Cell That Contains Specific Character?

Jul 17, 2012

Cells A1:A5 all have a vlookup formula that will pull one of the following into it:

1. The word "NONE"
2. #N/A error since it doesn't exist in the table
3. A text value that has the format 13.55.46.91

I want a formula that will look at these 5 cells and give me #3. That #3 value can only appear once in the 5 cells in any of those 5. The other 4 cells will contain either 1 or 2.

3 always has length equal to 11. It always has periods in those places. The digits do change.

View 3 Replies View Related

Extracting Data From Cell After Particular Character?

Sep 19, 2012

How di I extract charaters from a cell after a particular character/symbol

View 6 Replies View Related







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