How To Add A Letter In Front Of Each Number In A Cell 


How To Add A Letter In Front Of Each Number In A Cell  Excel 
View Answers 
I am working on this project & I cant figure out how to add a letter in
front of each number in a cell without clicking in to each individual cell
and typing it in. (very time consuming and tedious) The problem is  it's not
all the cells but a majority, all of the numbers are in the same column. To
explain, these are documents in storage. Each item is assigned a number (not
in any particular numerical order but they have to stay in the order they are.
Example:
X39655
X39656
X39711
39662
39664
39665
The last three numbers need the X in front of them.
Any ideas? Thanks so much!!
I read this previous reply and I'm not getting it.......I'm Excel challenged
:(
Use a help formula
=Sheet1!A1&"scc"
copy down/across, then copy and paste special as values in place ,
finally replace the old values with the new
Regards,
Peo Sjoblom
front of each number in a cell without clicking in to each individual cell
and typing it in. (very time consuming and tedious) The problem is  it's not
all the cells but a majority, all of the numbers are in the same column. To
explain, these are documents in storage. Each item is assigned a number (not
in any particular numerical order but they have to stay in the order they are.
Example:
X39655
X39656
X39711
39662
39664
39665
The last three numbers need the X in front of them.
Any ideas? Thanks so much!!
I read this previous reply and I'm not getting it.......I'm Excel challenged
:(
Use a help formula
=Sheet1!A1&"scc"
copy down/across, then copy and paste special as values in place ,
finally replace the old values with the new
Regards,
Peo Sjoblom
Similar Excel Tutorials
Capitalize First Letter of Every Word in a Cell  PROPER Function
In Excel you can use a function to capitalize the first letter of every word in a cell. This allows you to prepare ...
In Excel you can use a function to capitalize the first letter of every word in a cell. This allows you to prepare ...
Select Cells in Excel using Macros and VBA
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...
Keep Leading Zeros in Numbers in Excel  2 Ways
I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These two methods are very simple ...
I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These two methods are very simple ...
Make Column Headings Numbers instead of Letters  Make R1C1 Style References in Excel 2007
In Microsoft Excel you reference columns as letters by default  A1, B3, C5, etc. But you can also reference the co ...
In Microsoft Excel you reference columns as letters by default  A1, B3, C5, etc. But you can also reference the co ...
Helpful Excel Macros
Excel Macro to Save a Specific Worksheet as a New File
 This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
 This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Replace Formulas with Values (For The Entire Workbook)
 This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
 This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Save the Current Worksheet as a New Excel Workbook File
 This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
 This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Format Cells in The Number (Numerical) Number Format in Excel
 This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
 This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Macro to add a New Line to Message Box Popup Windows in Excel
 This is a very simple Message Box, popup window, macro for Excel that illustrates how to put new lines, the same thi
 This is a very simple Message Box, popup window, macro for Excel that illustrates how to put new lines, the same thi
Similar Topics
Hi Everybody
I have an Excel Sheet "Sheet1" in Column A from Row 6 down to Row 140 I have
sequential numbers from 1 to 135. In Column B again from Row 6 down to Row
140 I have the single letters E, W, N in no particular order. Is it possible
to have a routine that will place a number 1 in frint of the First letter E
it finds e.g. 1E and when it finds the next E it places 2 in front of it e.g
2E and it would continue until it finds the last letter E placing the
sequential number in front as per the example. The routine would then
continue and do the same for the other 2 Letters N, W and begin each with the
number 1.
Hope I've made myself clear

Many thanks
hazel
I have an Excel Sheet "Sheet1" in Column A from Row 6 down to Row 140 I have
sequential numbers from 1 to 135. In Column B again from Row 6 down to Row
140 I have the single letters E, W, N in no particular order. Is it possible
to have a routine that will place a number 1 in frint of the First letter E
it finds e.g. 1E and when it finds the next E it places 2 in front of it e.g
2E and it would continue until it finds the last letter E placing the
sequential number in front as per the example. The routine would then
continue and do the same for the other 2 Letters N, W and begin each with the
number 1.
Hope I've made myself clear

Many thanks
hazel
Hi everyone.
This should be a simple question.
In column A there is a list of numbers.
for example:
A1 has 1378130
A2 has 1378249
A3 has 1378881
A4 has S016957
A5 has 1377540
A6 has S016745
A7 has 1379059
etc...
What formula can i put in cell B1 that will look at A1:A150 that can tell me the number of cells in that range that have the letter "S" in front of the number ? In this example the answer should be two.
Any ideas ?
This should be a simple question.
In column A there is a list of numbers.
for example:
A1 has 1378130
A2 has 1378249
A3 has 1378881
A4 has S016957
A5 has 1377540
A6 has S016745
A7 has 1379059
etc...
What formula can i put in cell B1 that will look at A1:A150 that can tell me the number of cells in that range that have the letter "S" in front of the number ? In this example the answer should be two.
Any ideas ?
I need to add one column into another..
i have a Column of numbers that all need to have a letter in front of them;
right now: 1, 10, 13, 14...etc
needs to be PS1, PS10, PS13, PS14..etc
The column does not go in numerical order so i cannot just drag the letter down. Is there a way to add these letter w/o having to copy and paste it into every row? Please help, i am trying to make a deadline!
i have a Column of numbers that all need to have a letter in front of them;
right now: 1, 10, 13, 14...etc
needs to be PS1, PS10, PS13, PS14..etc
The column does not go in numerical order so i cannot just drag the letter down. Is there a way to add these letter w/o having to copy and paste it into every row? Please help, i am trying to make a deadline!
I use Excel 07 with Windows XP. I need to display an equation in a cell and have it just show the total. Fox example, the cell with show 100,000 and when you click on the cell you will see =25+50+10+15. What I am doing now is a get updated figures in pdf format every day with around 100 items so I copy and paste the data into notepad and then open it in Excel. My first sheet gets the data in order. Then on the next sheet I organize the data into 10 categories. Let's say that I have in column A, rows 14, I have 25, 50, 10, and 15. Then in cell B1, I have a formula, =A1+A2+A3+A4. Then under that in column B2 I have a macro that copy cell A1 and pastes it special as values. The macro does this for all 10 of my categories. Some categories have 4 values, some have 10. So in cell B2, I would have 25+50+10+5 but there will be know equals in the front. As of now, I just go through all the categories and put an equals sign in front of all the strings. Then I copy the strings and paste special as formulas into the columns of my report that I give to my managers each day. They want to be able to see the values in the cell when they look. Does this make sense? Can I have a macro that adds an equals in front of each string? The problem I have when I tried to do the macro to add the equals is the next day when the macro runs, the formula ends up having yesterday's values instead of the new values from this day.
Good Afternoon  thanks for looking
I have a worksheet to keep track of products. I use an SKU column with a Unique Number to keep track of those items on the shelves.
When I started my project I never imagined that my database would grow as large as it has. I started my SKU numbers with 80000, never suspecting that I would get to 90000. I am now at 125700. Many items have been removed / sold so it only encompases only 15500 rows.
On the site that I sell these Items, the SKU's when displayed start with 100000 and go to 125700 where 80000 is next and goes to 99999. ?? ( or the reverse depending on which tab I choose ) Not sure why this is but there is nothing I can do to change the way they do it so I must find a way to change my system. With all the 80000  99999 items numbered  changing them to 6 digit 125700+ numbers would be a huge undertaking so I would like to add a 0 in front of each 5 digit Number in my SKU Column. That way my items will show 080000  125700 instead of starting in the middle.
I do keep the column my ascending order so it is currently formated as a Number. I do at times copy an paste or pull ranges items by SKU numbers to mark down or modify.
When I place a 0 manually in front of 80000  it disappears when I move from the Cell.
If I change it to a TEXT cell  it stays in place.
Excel help doesnt answer my dilemma  nor does my book.
Can anyone help me to figure out what the best thing to do is and how to accomplish it.
I see there are masks etc  or is just text OK ? ( as I stated  I do use an numbered order or range to identify groups of items at times )  if text is OK, what is the best way to add a 0 to the start of each 5 digit number other than individually ?
There are Gaps in my sequence so I cannot just replace the first cell with 080000 and pull down.
Its a lot of questions but I wanted to explain what it is I'm trying to do.
Thanks for looking
I have a worksheet to keep track of products. I use an SKU column with a Unique Number to keep track of those items on the shelves.
When I started my project I never imagined that my database would grow as large as it has. I started my SKU numbers with 80000, never suspecting that I would get to 90000. I am now at 125700. Many items have been removed / sold so it only encompases only 15500 rows.
On the site that I sell these Items, the SKU's when displayed start with 100000 and go to 125700 where 80000 is next and goes to 99999. ?? ( or the reverse depending on which tab I choose ) Not sure why this is but there is nothing I can do to change the way they do it so I must find a way to change my system. With all the 80000  99999 items numbered  changing them to 6 digit 125700+ numbers would be a huge undertaking so I would like to add a 0 in front of each 5 digit Number in my SKU Column. That way my items will show 080000  125700 instead of starting in the middle.
I do keep the column my ascending order so it is currently formated as a Number. I do at times copy an paste or pull ranges items by SKU numbers to mark down or modify.
When I place a 0 manually in front of 80000  it disappears when I move from the Cell.
If I change it to a TEXT cell  it stays in place.
Excel help doesnt answer my dilemma  nor does my book.
Can anyone help me to figure out what the best thing to do is and how to accomplish it.
I see there are masks etc  or is just text OK ? ( as I stated  I do use an numbered order or range to identify groups of items at times )  if text is OK, what is the best way to add a 0 to the start of each 5 digit number other than individually ?
There are Gaps in my sequence so I cannot just replace the first cell with 080000 and pull down.
Its a lot of questions but I wanted to explain what it is I'm trying to do.
Thanks for looking
I get a descending order when I have letter in front of the numeral, but when I try just the number, all it gives me is a copy of the number.
Example:
Column Cell, input
a1, a1
a2,a2
a3, if I dragged down a2 will give a3, ect.
But if I input just numbers all it does is copy the last number.
Any have a solution for me?
Thanks
Example:
Column Cell, input
a1, a1
a2,a2
a3, if I dragged down a2 will give a3, ect.
But if I input just numbers all it does is copy the last number.
Any have a solution for me?
Thanks
i am sure this is probably really easy......i have to copy and paste values from an sap program over to excel spreadsheets, and I usually do about 15 at a time that end up in a column: 15 different cells. The value I am copying are ID numbers that all begin with zero and excel automatically removes the zeros at the front of each number.
Is there a formula/process for preventing this.
Thanks guys!
Is there a formula/process for preventing this.
Thanks guys!
Hello,
I'm trying to do this for ages.
Basically, in sheet1, column D, I have a list of names and numbers as follow:
bear  035888877 MS  02555887 Coal  58887441 SE  02587410
In Sheet2, column Q, I want to extract the number and keep the zeros in front (in case there is a zero). I've tried,
=REPLACE(LEFT(D1,LOOKUP(10,MID(A1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(D1&0,{1,2,3,4,5,6,7,8,9},0)))1,"")+0 (I found it online).
But I cannot keep the zeros in front! I tried changing cell format to text, didn't help either. I cannot do cutom0000000 as the lenth of the number can change.
I'm trying to build a macro (recording) doing this but the pb is always the little 0. Please advise if you have an idea?
Many thanks. Fak
I'm trying to do this for ages.
Basically, in sheet1, column D, I have a list of names and numbers as follow:
bear  035888877 MS  02555887 Coal  58887441 SE  02587410
In Sheet2, column Q, I want to extract the number and keep the zeros in front (in case there is a zero). I've tried,
=REPLACE(LEFT(D1,LOOKUP(10,MID(A1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(D1&0,{1,2,3,4,5,6,7,8,9},0)))1,"")+0 (I found it online).
But I cannot keep the zeros in front! I tried changing cell format to text, didn't help either. I cannot do cutom0000000 as the lenth of the number can change.
I'm trying to build a macro (recording) doing this but the pb is always the little 0. Please advise if you have an idea?
Many thanks. Fak
i have a spreadsheet with lots of numbers downloaded from our AS$)) system.
unfortunately, the columns have valid numbers and numbers like "******.**CR"
(* = a valid number).
i cannot get excel to convert the "CR" at the end of the number to a minus
sign in the front of the number.
any ideas?
unfortunately, the columns have valid numbers and numbers like "******.**CR"
(* = a valid number).
i cannot get excel to convert the "CR" at the end of the number to a minus
sign in the front of the number.
any ideas?
Hi all,
I've looked high and low for the answer to this but just can't find it.
I've got a whole heap of GPS waypoint data I have to manage each week. After bringing them into Excel I need to simply put one letter in front of the unique id numbers in one column. I can do it by changing the first cell and dragging it down but because the numbers are not always in sequence I need to be able to just highlight the whole column and somehow put a letter in front of each number in each cell WITHOUT losing that unique number.
Can anyone help this very frustrated Kiwi ??
Thanks,
Matt.
I've looked high and low for the answer to this but just can't find it.
I've got a whole heap of GPS waypoint data I have to manage each week. After bringing them into Excel I need to simply put one letter in front of the unique id numbers in one column. I can do it by changing the first cell and dragging it down but because the numbers are not always in sequence I need to be able to just highlight the whole column and somehow put a letter in front of each number in each cell WITHOUT losing that unique number.
Can anyone help this very frustrated Kiwi ??
Thanks,
Matt.
i am trying to use a wildcard with numbers but it will not work unless i put a letter in front of the number Below is the formula i am using. Ast you can see N814 is the numer i am using. i am doing a search and sum. If there is a any combo of numbers in column A between 8140 and 8149 it sums column D that match the number. however, as stated i have to put the letter in front or it does not find the number.
=SUMIF('SECTION 4'!A117:'SECTION 4'!A159,"N814?",'SECTION 4'!D116:'SECTION 4'!D159)
=SUMIF('SECTION 4'!A117:'SECTION 4'!A159,"N814?",'SECTION 4'!D116:'SECTION 4'!D159)
Hello there,
The Encrypting.xlsm worksheet is a project that encrypts text, turning letters into numbers, numbers that are produced by assigning a key that provide variables to complex excel calculations.
Its purpose is to automatically "encrypt" or translate letters to words or viceversa, according to the key used.
I need a button in the "Encryptor" sheet that is assigned with a macro that when triggered:
Will take the text, letter by letter, within a cell and paste the equivalent number for each letter into another cell.
And.. Will do the reverse process simultaneously: Take the encrpyted message from another cell, number by number (separated by a space), and paste the equivalent letter for each number into another cell.
The numbers equivalent for each letter are shown on column D from row 2 to row 28, in the "number" sheet.
I had a tough time imagining how would this project work and spent some additional effort preparing the formulas. All I need is the macro, which is quite a challenge.
If you could lend me a hand on the macro it would be great, and you'll have an encryption worksheet for your own!
The Encrypting.xlsm worksheet is a project that encrypts text, turning letters into numbers, numbers that are produced by assigning a key that provide variables to complex excel calculations.
Its purpose is to automatically "encrypt" or translate letters to words or viceversa, according to the key used.
I need a button in the "Encryptor" sheet that is assigned with a macro that when triggered:
Will take the text, letter by letter, within a cell and paste the equivalent number for each letter into another cell.
And.. Will do the reverse process simultaneously: Take the encrpyted message from another cell, number by number (separated by a space), and paste the equivalent letter for each number into another cell.
The numbers equivalent for each letter are shown on column D from row 2 to row 28, in the "number" sheet.
I had a tough time imagining how would this project work and spent some additional effort preparing the formulas. All I need is the macro, which is quite a challenge.
If you could lend me a hand on the macro it would be great, and you'll have an encryption worksheet for your own!
I have multiple (incredible number of) references that need to be made across different worksheets.
Example: Right now, cell B1 contains: ='[070109.xls]FRONT'!$B$16
Now, if B2 contained: =B1
Then I could click on the bottom right of the cell, and copy it to the right perpetually. I could stop at column J, and the formula would read: =J1
Obviously, this doesn't work the same way.
I would LIKE for C1 to contain: ='[070109.xls]FRONT'!$B$17
D1 to contain: ='[070109.xls]FRONT'!$B$18
etc, on through to: ='[070109.xls]FRONT'!$B$49
I would like to find an easy way to do this, as I now have to track assignments, and have to backtrack to the beginning of the year.
This is going to result in 365 days worth of tracking 25+/ individuals, so I'm thinking about doing 12 workbooks (1 per month), with, of course, 30+/ days worth of tracking per.....either way....
Going to be rather a pain in the ***, but hey.
Is there a way to formulate something to the effect of...
...say this is in B1: ='[070109.xls]FRONT'!$B$16
Can something be done in C1 that 'adds' a row to the formula in B1?
(='[070109.xls]FRONT'!$B$16 + 1 row)
Sounds much more simplistic than it likely is, but I have to ask.
Thanks for any help!
Example: Right now, cell B1 contains: ='[070109.xls]FRONT'!$B$16
Now, if B2 contained: =B1
Then I could click on the bottom right of the cell, and copy it to the right perpetually. I could stop at column J, and the formula would read: =J1
Obviously, this doesn't work the same way.
I would LIKE for C1 to contain: ='[070109.xls]FRONT'!$B$17
D1 to contain: ='[070109.xls]FRONT'!$B$18
etc, on through to: ='[070109.xls]FRONT'!$B$49
I would like to find an easy way to do this, as I now have to track assignments, and have to backtrack to the beginning of the year.
This is going to result in 365 days worth of tracking 25+/ individuals, so I'm thinking about doing 12 workbooks (1 per month), with, of course, 30+/ days worth of tracking per.....either way....
Going to be rather a pain in the ***, but hey.
Is there a way to formulate something to the effect of...
...say this is in B1: ='[070109.xls]FRONT'!$B$16
Can something be done in C1 that 'adds' a row to the formula in B1?
(='[070109.xls]FRONT'!$B$16 + 1 row)
Sounds much more simplistic than it likely is, but I have to ask.
Thanks for any help!
Ok I am typing in a bunch of UPC numbers for my data sheet, It will be a
couple hundred numbers. The UPC numbers are as follows
012569528628
027616149237
025192051029
012235111536
012569528734
012569508422
12569508439
86162180934
043396039490
043396603820
012569509023
012569509030
12569512429
12569512436
For some odd reason all the numbers without a zero are wrong. All of these
numbers need 1 number 0 in front of them. As for the other numbers I get a
little green arrow in the upper left corner of the cell. It tells me
Something about "Number stored as text" (This is the one that is
automatically highlighted when i click the error tag) it gives me a couple
other options such as "Convert to Number" "Help on this Error"(Gets me no
where) "Ignore Error" which seems to be the only thing that allows the number
0 to stay. If I do the convert to number it removes the number 0. How can I
set the entire cell column to leave the number 0 infront of the number. I
have spent 45 minutes now trying to figure this out. Thanks for any help you
can provide me.
couple hundred numbers. The UPC numbers are as follows
012569528628
027616149237
025192051029
012235111536
012569528734
012569508422
12569508439
86162180934
043396039490
043396603820
012569509023
012569509030
12569512429
12569512436
For some odd reason all the numbers without a zero are wrong. All of these
numbers need 1 number 0 in front of them. As for the other numbers I get a
little green arrow in the upper left corner of the cell. It tells me
Something about "Number stored as text" (This is the one that is
automatically highlighted when i click the error tag) it gives me a couple
other options such as "Convert to Number" "Help on this Error"(Gets me no
where) "Ignore Error" which seems to be the only thing that allows the number
0 to stay. If I do the convert to number it removes the number 0. How can I
set the entire cell column to leave the number 0 infront of the number. I
have spent 45 minutes now trying to figure this out. Thanks for any help you
can provide me.
Hi all,
I attach here an Excel file where I ask you help to do something.
As you can see, in column AO I have a series of numbers. What I would like to do, is a VBA code that could read that number, and if that number is the same that the row number: Ok, don't do nothing. If that number is different from the row number then, it should copy all values from the "greennumber" row to the row where that number is. A little bit confused right?
An example:
Read AO46. The number read is 48. Then It should copy all values from row 48 to row 46.
Next.
Read AO47. The number read is 49. Then it should copy all values from row 49 to row 47.
Next.
Read AO48. The number read is 52. Then it should copy all values from row 52 to row 48.
....
And this until the end of the sheet. Can someone give me a help out?
Thank you all
help.xlsm
How do I add a letter in front of a column of numbers.
I have a column of stock part numbers and I want to identify them as belonging to a particular supplier by adding a D in front of all the numbers.
There are about a thousand lines, what is the easiest way to do this.
I have a column of stock part numbers and I want to identify them as belonging to a particular supplier by adding a D in front of all the numbers.
There are about a thousand lines, what is the easiest way to do this.
hi,
I have 2 columns, 1 with a list of order numbers, and a column next to it with return values (please see attached file).
as you can see the current formula i have is '=IF((FIND("V",I2)),"VO","No")', so this searches for the letter V in the order number column, and if found, returns the value 'VO'. Instead of returning 'No' for false, it simply returns an error instead, #VALUE!.
Obviously i'm doing something wrong but i cannot figure out what. All I want it to do is return "True" if it can find the letter V in the order number column, or "False" if it can't.
Also in the pivot table its returning an option of '10' for that column, I presume thats because it's finding the letter 'V' as the 10th character in the cell, but I only want the column to show true or false.
Would appreciate some help with this,
thanks
I have 2 columns, 1 with a list of order numbers, and a column next to it with return values (please see attached file).
as you can see the current formula i have is '=IF((FIND("V",I2)),"VO","No")', so this searches for the letter V in the order number column, and if found, returns the value 'VO'. Instead of returning 'No' for false, it simply returns an error instead, #VALUE!.
Obviously i'm doing something wrong but i cannot figure out what. All I want it to do is return "True" if it can find the letter V in the order number column, or "False" if it can't.
Also in the pivot table its returning an option of '10' for that column, I presume thats because it's finding the letter 'V' as the 10th character in the cell, but I only want the column to show true or false.
Would appreciate some help with this,
thanks
I have a list of with numbers & blanks. For all the numbers, I would like to put the equal sign "=" in front of it.
For if I have 234 in cell, I want it to be =234.
There are several thousands of values in the list.
What I am doing presently is to concatenate a e.g. "X" in front of it then later find & replace "X" with a "=".
Is there a better way to do this?
For if I have 234 in cell, I want it to be =234.
There are several thousands of values in the list.
What I am doing presently is to concatenate a e.g. "X" in front of it then later find & replace "X" with a "=".
Is there a better way to do this?
I want them to be able to copy and paste cell values without it also copying and pasting the validation. This is because I have different validation settings for every single cell, and don't want to change those everytime I copy and paste. I know that you can use paste special to paste values without validations, but that will get tedious, and it doesn't seem to work anyway  when I paste special the value then its as if the paste special overrides whatever validation criteria I was using. Is there a setting or a trick to be able to paste values only all the time without using paste special, AND when I paste values only I dont want it to override the validation I have set? For example, if I have validation set for only allowing numbers between 0 and 2 and input 54 manually an error message occurs of course, but if I copy and paste special the number 54 into the cell it then allows that to happen despite setting validation to only allow numbers between 0 and 2, and despite pasting special values only.
Hi,
I have a collum with different numbers in each row.
Example:
23
34
56
67
44
I'd like to add the letter P before each number all at once rather then typing
P before each one manually.
Example:
P23
P34
P56
P67
P44
Was wondering to go about doing this?
Thanks
Dino
I have a collum with different numbers in each row.
Example:
23
34
56
67
44
I'd like to add the letter P before each number all at once rather then typing
P before each one manually.
Example:
P23
P34
P56
P67
P44
Was wondering to go about doing this?
Thanks
Dino
Hello All,
I have found that excel can be the best thing ever and also a nightmare when you are stuck on something. I really apprecciat anyone who can help me out.
In an excel workbook i have a sheet that has rows and rows of Sales Leads.
In column A i put a letter "C" if they have turned into a client or an "X" if the sale was not made.
I am looking for a function that will look at column A and when it sees the letter "C" then copy the entire row across and put the row into sheet 2.
For example Sheet 1 has
A B C
1 C Client1 etc
2 X Client2 etc
3 C Client3 etc
Sheet 2 should then read:
A B C
1 C Client1 etc
1 C Client3 etc
Sheet 2 should show only client1 and client3 and the information to the right of them because they have a letter "C" in front of them.
The below function is how far i got. Maybe the function can be fixed or maybe you can suggest something new.
=VLOOKUP($A4,Sheet1!$A$2:$M$16000,COLUMN(B:B),0)
The prolem with the above funtion is when i copy it to multiple cells
It returns the inormation of the first customer only. Because the funcion tells it to Find "C" and when it does then it only returns information of Client1 and repeats Client1 over and over. How can i get it to return all of the clients with letter c in front.
Maybe you can help me fix the above funciton or perhaps i have gone down the completely wrong path and you can suggest something new.
Thanks to all who have even read this far and those who reply.
Best,
Atlantic
I have found that excel can be the best thing ever and also a nightmare when you are stuck on something. I really apprecciat anyone who can help me out.
In an excel workbook i have a sheet that has rows and rows of Sales Leads.
In column A i put a letter "C" if they have turned into a client or an "X" if the sale was not made.
I am looking for a function that will look at column A and when it sees the letter "C" then copy the entire row across and put the row into sheet 2.
For example Sheet 1 has
A B C
1 C Client1 etc
2 X Client2 etc
3 C Client3 etc
Sheet 2 should then read:
A B C
1 C Client1 etc
1 C Client3 etc
Sheet 2 should show only client1 and client3 and the information to the right of them because they have a letter "C" in front of them.
The below function is how far i got. Maybe the function can be fixed or maybe you can suggest something new.
=VLOOKUP($A4,Sheet1!$A$2:$M$16000,COLUMN(B:B),0)
The prolem with the above funtion is when i copy it to multiple cells
It returns the inormation of the first customer only. Because the funcion tells it to Find "C" and when it does then it only returns information of Client1 and repeats Client1 over and over. How can i get it to return all of the clients with letter c in front.
Maybe you can help me fix the above funciton or perhaps i have gone down the completely wrong path and you can suggest something new.
Thanks to all who have even read this far and those who reply.
Best,
Atlantic
I have a worksheet that a user fills out. One cell in particular is filled out in "code". The code is made up of letters and numbers. IE  in E2 the user will enter " 2E5, 3a5, 1g7, ..." What I need to do via VBA is to take each set (sets are separated by commas) and put the "decoded" value into a different sheet. I have two lists to look up the values, Defects and Location of Defects. The code will always be number, letter, number. The first number represents how many, the letter represents the defect and the third "digit" represents the location of the defect. So, 2E5 would translate into " 2 burn marks front face " . I would need the value " 2 burn marks front face " to be put into a cell on a different sheet. The next value or set in line would be put into the cell below and so forth. If there was only one "code" then I wouldn't have a problem, but since there are several that could be put in, I need the code to separate each code automatically. Hope I haven't confused anyone more with my explanation.
Tony
Tony
Hello All,
I have found that excel can be the best thing ever and also a nightmare when you are stuck on something. I really apprecciat anyone who can help me out.
In an excel workbook i have a sheet that has rows and rows of Sales Leads.
In column A i put a letter "C" if they have turned into a client or an "X" if the sale was not made.
I am looking for a function that will look at column A and when it sees the letter "C" then copy the entire row across and put the row into sheet 2.
For example Sheet 1 has
A____B________C_____D
1____C____Client1____etc
2____X____Client2____etc
3____C____Client3____etc
Sheet 2 should then read:
A____B_______C______D
1____C____Client1____etc
1____C____Client3____etc
Sheet 2 should show only client1 and client3 and the information to the right of them because they have a letter "C" in front of them. Cleint2 is not copied over because it has an "X" in column A.
The below function is how far i got. Maybe the function can be fixed or maybe you can suggest something new.
=VLOOKUP($A4,Sheet1!$A$2:$M$16000,COLUMN(B:B),0)
The prolem with the above funtion is when i copy it to multiple cells
It returns the inormation of the first customer only. Because the funcion tells it to Find "C" and when it does then it only returns information of Client1 and repeats Client1 over and over. How can i get it to return all of the clients with letter c in front.
Maybe you can help me fix the above funciton or perhaps i have gone down the completely wrong path and you can suggest something new.
Thanks to all who have even read this far and those who reply.
Best,
Atlantic
I have found that excel can be the best thing ever and also a nightmare when you are stuck on something. I really apprecciat anyone who can help me out.
In an excel workbook i have a sheet that has rows and rows of Sales Leads.
In column A i put a letter "C" if they have turned into a client or an "X" if the sale was not made.
I am looking for a function that will look at column A and when it sees the letter "C" then copy the entire row across and put the row into sheet 2.
For example Sheet 1 has
A____B________C_____D
1____C____Client1____etc
2____X____Client2____etc
3____C____Client3____etc
Sheet 2 should then read:
A____B_______C______D
1____C____Client1____etc
1____C____Client3____etc
Sheet 2 should show only client1 and client3 and the information to the right of them because they have a letter "C" in front of them. Cleint2 is not copied over because it has an "X" in column A.
The below function is how far i got. Maybe the function can be fixed or maybe you can suggest something new.
=VLOOKUP($A4,Sheet1!$A$2:$M$16000,COLUMN(B:B),0)
The prolem with the above funtion is when i copy it to multiple cells
It returns the inormation of the first customer only. Because the funcion tells it to Find "C" and when it does then it only returns information of Client1 and repeats Client1 over and over. How can i get it to return all of the clients with letter c in front.
Maybe you can help me fix the above funciton or perhaps i have gone down the completely wrong path and you can suggest something new.
Thanks to all who have even read this far and those who reply.
Best,
Atlantic
Hello, please help how I can sort my cells not only in alphabetical order but also by numbers and letters in the cell.
For example I have a cell values like e24015C. I want excel to sort cells by first letter first (alphabetical order) but than by number on the second place and after that by number on the third place etc.
So I could have cells with the same first letter also sorteb by remaining digits.
Thanks a lot for your help
Hi there,
..I have an annoying problem that crops up from time to time regarding number formatting.
I copy and paste "special" to transfer some calculated numbers into values. However these seems to paste the numbers as "text", and subsequent calculations do not then work.
I have tried highlighting the numbers as "reformating" them as numbers, but this does not work. I then physically have to retype each number back into the cell for exdcel to recognise that they are asctually numbers and not text?
Is there a quick way to "copy" or generate a function that will convert "text" numbers to actual "numbers".
Mnay thanks,
Stu
..I have an annoying problem that crops up from time to time regarding number formatting.
I copy and paste "special" to transfer some calculated numbers into values. However these seems to paste the numbers as "text", and subsequent calculations do not then work.
I have tried highlighting the numbers as "reformating" them as numbers, but this does not work. I then physically have to retype each number back into the cell for exdcel to recognise that they are asctually numbers and not text?
Is there a quick way to "copy" or generate a function that will convert "text" numbers to actual "numbers".
Mnay thanks,
Stu