Have User Input Converted To Uppercase In Same Cell As Input? |
Have User Input Converted To Uppercase In Same Cell As Input? - Excel |
|
Hi all
Noob Alert! Very new to spreadsheets! I have a simple form that requires
data selected from a text description to be entered in a cell to give us a
special product code.
The code is upper case in the description but ppl are entering it in lower
case, I need to convert the entry to uppercase to match our entry system, but
I need a function to convert and display the converted text in the original
cell.
I looked at =UPPER function but that displays converted text elsewhere, if I
use it in the same cell as input I want converted I get the circular error
msg.
Can someone perhaps take time to show me how to make a drop down selection
where I can control the selection output please? That would look better and
solve the dilemma.
Thanks in advance
ShannonN
Noob Alert! Very new to spreadsheets! I have a simple form that requires
data selected from a text description to be entered in a cell to give us a
special product code.
The code is upper case in the description but ppl are entering it in lower
case, I need to convert the entry to uppercase to match our entry system, but
I need a function to convert and display the converted text in the original
cell.
I looked at =UPPER function but that displays converted text elsewhere, if I
use it in the same cell as input I want converted I get the circular error
msg.
Can someone perhaps take time to show me how to make a drop down selection
where I can control the selection output please? That would look better and
solve the dilemma.
Thanks in advance
ShannonN
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Similar Topics
I am creating a data sheet to be completed by other users. I would like to
format the text cells (name, etc) to have text entered as uppercase
automatically although the user might use title or lower case.
UPPER function cannot make cell look at itself and perform the function
Excel 2003
format the text cells (name, etc) to have text entered as uppercase
automatically although the user might use title or lower case.
UPPER function cannot make cell look at itself and perform the function
Excel 2003
From limited experience I know that excel calculates dates via serial numbers.
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
I am looking for assistance in having one cell in a text format equals another cell that contains a time value in hh:mm format.
For example: Cell A1 has a time format (hh:mm) value of 04:00; which is the Start Time. I would like cell D1 to have a text format value of "04:00" (result is dependant upon what is entered in A1). I would duplicate the same formulas to reflect Stop Times in other cells.
My final result is to have another cell (F1) use the Concatenate formula to have the Start and Stop time shown in one cell as "04:00 - 12:30". The times would change based on the Time formated values entered into the Start and Stop time cells.
I have researched this in the board and found many excellent ways to do the opposite, but not convert Time format to Text format. Any assistance is greatly appreciated.
-Shane
For example: Cell A1 has a time format (hh:mm) value of 04:00; which is the Start Time. I would like cell D1 to have a text format value of "04:00" (result is dependant upon what is entered in A1). I would duplicate the same formulas to reflect Stop Times in other cells.
My final result is to have another cell (F1) use the Concatenate formula to have the Start and Stop time shown in one cell as "04:00 - 12:30". The times would change based on the Time formated values entered into the Start and Stop time cells.
I have researched this in the board and found many excellent ways to do the opposite, but not convert Time format to Text format. Any assistance is greatly appreciated.
-Shane
Hi All,
I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.
It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.
Please can someone give me a bit of guidence or link me to a good rescource if missed it in the search.
Thanks in advance.
G
I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.
It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.
Please can someone give me a bit of guidence or link me to a good rescource if missed it in the search.
Thanks in advance.
G
I was wondering if anyone could help me out please?!?!
I need to have a cell on Sheet2 display the TEXT from a cell on Sheet1, is his possible??
Example:
Sheet1, Cell A1 contains a clients name and I would like to have Sheet2, Sheet3, Sheet4 etc... Display that clients name in a cell of my choosing (could be a different cell on each Sheet) automatically after entering it once on Sheet1, cell A1.
I thought this was possible but I can't seem to figure it out. I haven't done any real Excel work in quite a long time but I thought I had done this before a long time ago!! Any help would be GREATLY appreciated!! Thanks in advance...
P.S. If I use autosum to do this it just displays the number 0, obviously, since it is trying to add numbers...
I uploaded a copy of the spreadsheet, What I would like to happen is have the text from Sheet1,D4 automatically be placed in Sheet2,A1 and Sheet3, B2. Hope this helps
-Aric
I need to have a cell on Sheet2 display the TEXT from a cell on Sheet1, is his possible??
Example:
Sheet1, Cell A1 contains a clients name and I would like to have Sheet2, Sheet3, Sheet4 etc... Display that clients name in a cell of my choosing (could be a different cell on each Sheet) automatically after entering it once on Sheet1, cell A1.
I thought this was possible but I can't seem to figure it out. I haven't done any real Excel work in quite a long time but I thought I had done this before a long time ago!! Any help would be GREATLY appreciated!! Thanks in advance...
P.S. If I use autosum to do this it just displays the number 0, obviously, since it is trying to add numbers...
I uploaded a copy of the spreadsheet, What I would like to happen is have the text from Sheet1,D4 automatically be placed in Sheet2,A1 and Sheet3, B2. Hope this helps
-Aric
I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.
Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.
What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.
The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.
Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?
Many thanks.
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.
Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.
What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.
The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.
Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?
Many thanks.
Hi everyone, this is my first post here. I consider myself a more advanced Excel user and can navigate my way around fairly well. I've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
Can anyone help with this, because I can't figure it out....
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
Can anyone help with this, because I can't figure it out....
I have a textbox in my userform where anything typed in the box is entered into cell A2 in my worksheet. However, multiple lines of text are not properly displayed in my worksheet. I have enabled MultiLine and EnterKeyBehaviour in the properties window of the textbox so I can type multiple lines in my textbox. The problem is that when I press the button to enter the text from my txtbox to cell A2, the cell doesn't actually display the text in separate lines. It just places a square symbol in the place that enter should have been pressed and the text should have been split into separate lines.
How can I get the separate lines of text in my textbox to be properly displayed in a cell? Thanks for your help in advance..
How can I get the separate lines of text in my textbox to be properly displayed in a cell? Thanks for your help in advance..
Hi everyone,
I'm going nuts trying to figure out how to autofill text from one cell
to another. For example, everytime I type text into cell A20, I want
the exact same thing to cell BL20. The same for B20, BL20, etc.
How do I go about that? I already know how to copy formulas from one
cell to another by dragging the skinny black line of the cell. I guess
I just need the formula for a simple IDENTICAL copying of text.
Thanks in advance,
Mike
I'm going nuts trying to figure out how to autofill text from one cell
to another. For example, everytime I type text into cell A20, I want
the exact same thing to cell BL20. The same for B20, BL20, etc.
How do I go about that? I already know how to copy formulas from one
cell to another by dragging the skinny black line of the cell. I guess
I just need the formula for a simple IDENTICAL copying of text.
Thanks in advance,
Mike
After entering a text in a cell or in the function field, when i go back in
the cell an apostrophe apear in front of the text.
How can i correct this?
Thank you
the cell an apostrophe apear in front of the text.
How can i correct this?
Thank you
Hi guys,
would really appreciate anyones help with this.
I have a column full of text-formatted fractions...
4/5
6/4
3/1
2/5
4/5
etc, etc.....the column is very long!!
I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.
If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!
Any suggestions on how I could speed this up?
thanks
would really appreciate anyones help with this.
I have a column full of text-formatted fractions...
4/5
6/4
3/1
2/5
4/5
etc, etc.....the column is very long!!
I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.
If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!
Any suggestions on how I could speed this up?
thanks
Hey there,
I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.
Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.
I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.
I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.
Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.
I'll post up further comments as I am trying to work my way through it!
Thanks,
Jag
I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.
Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.
I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.
I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.
Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.
I'll post up further comments as I am trying to work my way through it!
Thanks,
Jag
Hello all,
Until now I have been able to find all my answers through searches. As a VBA novice, it has been very helpful. I am stumped on this one, however. I am trying to autofill from the selected cell in Column C down. I would like it to stop at the last cell with data in Column B. This is the code I have so far:
Range("C2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
This does the autofill, but doesn't stop at the last cell with data in Column B.
In the past I have use this code to acheive similar results:
Dim endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & endRow)
The problem with this code is that I will not always be starting in "C2". I need code that uses whatever the selected cell is.
All help is appreciated. Thanks!
Until now I have been able to find all my answers through searches. As a VBA novice, it has been very helpful. I am stumped on this one, however. I am trying to autofill from the selected cell in Column C down. I would like it to stop at the last cell with data in Column B. This is the code I have so far:
Range("C2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
This does the autofill, but doesn't stop at the last cell with data in Column B.
In the past I have use this code to acheive similar results:
Dim endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2").AutoFill Destination:=Range("C2:C" & endRow)
The problem with this code is that I will not always be starting in "C2". I need code that uses whatever the selected cell is.
All help is appreciated. Thanks!
Posted this on the Ozgrid forums, but haven't gotten any help yet, so I thought I'd try here too!
I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.
I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?
______________________
Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.
If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...
Any help would be much appreciated. This is driving me nuts!
I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.
I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?
______________________
Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.
If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...
Any help would be much appreciated. This is driving me nuts!
I have been using Excel (XP) to make a text chart for several months. Some of
my text entries are rather lengthy. The past two weeks these lengthy entries
are showing up as pound signs (#########) when I click off the cell. I know
the text will fit in the cell, and the problem isn't solved by making the
cell bigger or using a little bit less text. I have the cells formatted as
"text" and "wrap to fit". I have printed the pages and the printed version
also has pound signs. I just want my text to show up!
my text entries are rather lengthy. The past two weeks these lengthy entries
are showing up as pound signs (#########) when I click off the cell. I know
the text will fit in the cell, and the problem isn't solved by making the
cell bigger or using a little bit less text. I have the cells formatted as
"text" and "wrap to fit". I have printed the pages and the printed version
also has pound signs. I just want my text to show up!
I run a small business. I want to start keeping track of inventory using a barcode system.
I know Excel "ok", but I don't know programming.'
I'd like to be able to do something very simple (for now)...
For example...if I had PRODUCT / CODE / INV VALUE
A - CODE A - 5
B - CODE B - 11
C - CODE C - 14
I would want to be able to click a button (or scan a code for) : ADD +1 or REMOVE -1
...then scan the barcode on the product (A) and have it add or remove one, respectively. So, if the row for A is:
A - CODE A - 5
I choose "ADD" (with a button or scan code), then scan the barcode, and the spreadsheet automatically finds the code in the sheet, and changes the corresponding inventory value:
A - CODE A - 6
Does that make sense? How might I go about doing that?
I know Excel "ok", but I don't know programming.'
I'd like to be able to do something very simple (for now)...
For example...if I had PRODUCT / CODE / INV VALUE
A - CODE A - 5
B - CODE B - 11
C - CODE C - 14
I would want to be able to click a button (or scan a code for) : ADD +1 or REMOVE -1
...then scan the barcode on the product (A) and have it add or remove one, respectively. So, if the row for A is:
A - CODE A - 5
I choose "ADD" (with a button or scan code), then scan the barcode, and the spreadsheet automatically finds the code in the sheet, and changes the corresponding inventory value:
A - CODE A - 6
Does that make sense? How might I go about doing that?
This formula should be very often to use, but i cant found it anywhere, so i ask this here
For example in a1 is :
mike is playing basketball, he is very great player
i want to make it in b1 to be uppercase only in first letter so :
Mike is playing basketball, he is very great player
i know about proper formula =proper(a1) , but proper formula is make uppercase to all first letter in all words, we just need first letter in first words to be uppercase (and to every first words after dot if it can)
For example in a1 is :
mike is playing basketball, he is very great player
i want to make it in b1 to be uppercase only in first letter so :
Mike is playing basketball, he is very great player
i know about proper formula =proper(a1) , but proper formula is make uppercase to all first letter in all words, we just need first letter in first words to be uppercase (and to every first words after dot if it can)
I would like to create a formula that removes a specific character if it appears in a cell. In this case, if the text in the name cell starts with * or #, remove it. Otherwise, keep the contents intact. Examples:
Code:
Is there a formula that will get me where I want to be?
Thanks!
Chuck
Code:
Text in Cell Desired Results *Bobby Abreu Bobby Abreu #Erick Aybar Erick Aybar Jason Bartlett Jason Bartlett
Is there a formula that will get me where I want to be?
Thanks!
Chuck
How do I calculate the number of batches per hour? Here's what I have so far:
Cell B5: start time: 4:15 formatted as 4:15:00 am
Cell B6: finish time: 6:15 formatted as 6:15:00 am
Cell B7: total time: 2:00 formula in cell: =TEXT(B6-B5,"h:mm")
Cell B8: total batches processed: 22 (this is entered manually)
Cell B9: batches per hour: formula in cell: =B8/TEXT(B7,"h")
as long as I have this formula in cell B9 the answer comes out correct which should be 11 per hour.
If the formula in B9 is B8/B7, the answer is 264.0, Is this because of the way excel is reading the total time or the total time is formatted as a time, not an actual number? Is this the correct way to solve the problem?
answer should be 11 per hour.
Cell B5: start time: 4:15 formatted as 4:15:00 am
Cell B6: finish time: 6:15 formatted as 6:15:00 am
Cell B7: total time: 2:00 formula in cell: =TEXT(B6-B5,"h:mm")
Cell B8: total batches processed: 22 (this is entered manually)
Cell B9: batches per hour: formula in cell: =B8/TEXT(B7,"h")
as long as I have this formula in cell B9 the answer comes out correct which should be 11 per hour.
If the formula in B9 is B8/B7, the answer is 264.0, Is this because of the way excel is reading the total time or the total time is formatted as a time, not an actual number? Is this the correct way to solve the problem?
answer should be 11 per hour.
Is there a way I can use conditional formatting or something to change the color of the cell once I enter a value or text into that cell? For example if I currently have the A2 cell color as a gray color, and I enter information into A2 can I have it set to automatically change to no fill or any other color without having to click out of the cell, back into it, and then clicking the button?? Does that make sense??? Can someone help me?