Change excel digital currency to text
Publish: 2021-05-01 07:12:24
1. If the data is in column a, you can enter the formula in B1:
= replace (A1, match (, 0 / mid (A1, row ($1): $99), 1),,, & quot; ￥")
press Ctrl + Shift + enter to end the formula, and then drop it down
= replace (A1, match (, 0 / mid (A1, row ($1): $99), 1),,, & quot; ￥")
press Ctrl + Shift + enter to end the formula, and then drop it down
2. That's your code
3. Format - & gt; Column - & gt; Column width --- enter a large number to see
if you can't, then format --- row --- row height --- enter a large number to see
if you can't, then format --- row --- row height --- enter a large number to see
4.
Long numbers in Excel will automatically become "scientific counting method". If you only need to display them as "text", you can use the following steps:
first, select all the data to be changed into text in Excel table
5. This is obviously not a double quotation mark in English. Please change it again. It's double quotation marks.
6. It's almost enough to master these
first, to create a category drop-down list filling item
we often need to input the name of the enterprise into the table. In order to maintain the consistency of the name, we use the "data validity" function to create a category drop-down list filling item
1. In SHEET2, enter the enterprise name into different columns by category (such as "instrial enterprise", "commercial enterprise", "indivial enterprise" and so on) to establish an enterprise name database
2. Select column a (the column of instrial enterprise name), enter the character "instrial enterprise" in the "name" column, and press enter to confirm
following the above operation, name columns B and C as "commercial enterprise" and "indivial enterprise"...
3. Switch to Sheet1, select the column (such as column C) that needs to input "enterprise category", execute the command "data → validity", and open the dialog box of "data validity". In the "Settings" tab, click the drop-down button on the right side of "allow", select the "sequence" option, and in the "source" box below, enter "instrial enterprise", "commercial enterprise", "indivial enterprise"... Sequence (each element is separated by a comma in English), and confirm to exit
select the column (e.g. column d) where you want to enter the enterprise name, and then open the data validity dialog box. After selecting the sequence option, enter the formula in the source box: = indirect (C1), and confirm to exit
4. Select any cell in column C (such as C4), click the drop-down button on the right, and select the corresponding "enterprise category" to fill in the cell. Then select the D column cell (such as D4) corresponding to the cell, and click the drop-down button to select the required enterprise name from the enterprise name list of the corresponding category and fill in the cell
prompt: if you do not need to print the "enterprise category" column in the future, you can select the column, right-click and select the "hide" option to hide the column< Second, create a new menu of "common documents"
create a new menu of "common documents" on the menu bar to add common workbooks to it, which is convenient to call at any time
1. Right click the mouse in the blank space of the toolbar and select the "Customize" option to open the "Customize" dialog box. In the command tab, select new menu under category, and then drag new menu under command to the menu bar
press the "change selection" button and enter a name (such as "common documents") in the "naming" box of the pop-up menu. 2. Select any item under "category" (such as "insert" option), and select any item under "command" on the right (such as "hyperlink" option), drag it to the new menu (common documents), and name it after the above operation (such as "payroll", etc.) to create the first document list name of the workbook
repeat the above operation to add more document list names
3. Select a menu item (such as payroll) in the common documents menu, right-click the mouse, and in the pop-up shortcut menu, select the "assign hyperlink → open" option to open the "assign hyperlink" dialog box. By pressing the drop-down button on the right side of "search range", locate the corresponding Workbook (such as "salary. XLS") folder, and select the workbook document
repeat the above operation to hyperlink the menu item with its corresponding workbook document
4. When you need to open a workbook document in the common documents menu in the future, just expand the common documents menu and click the corresponding option
tip: Although we drag the "hyperlink" option to the "common documents" menu, it does not affect the function of the "hyperlink" menu item in the "insert" menu and the "insert hyperlink" button on the "common" toolbar< Third, different types of data should be displayed in different colors.
in the payroll, if you want to display the total salary of 2000 yuan or more in red, 1500 yuan or more in blue, 1000 yuan or less in brown, and others in black, we can set it this way
1. Open the "payroll" workbook, select the column of "payroll", execute the "format → conditional format" command, and open the "conditional format" dialog box. Click the drop-down button on the right side of the second box, select the "greater than or equal to" option, and enter the value "2000" in the following box. Click format to open the cell Format dialog box, and set the color of font to red
2. Press the "add" button, and follow the above operation to set other conditions (greater than or equal to 1500, the font is set to "blue"; 2; Less than 1000, the font is set to "Brown")
3. After setting, press the "OK" button. Take a look at the payroll. Is the payroll data displayed in different colors according to your requirements< When editing professional forms, we often need to input some special professional symbols. In order to facilitate input, we can make our own "professional symbols" toolbar
1. Execute the command "tools → macro → record new macro", open the dialog box of "record new macro", and enter the macro name
first, to create a category drop-down list filling item
we often need to input the name of the enterprise into the table. In order to maintain the consistency of the name, we use the "data validity" function to create a category drop-down list filling item
1. In SHEET2, enter the enterprise name into different columns by category (such as "instrial enterprise", "commercial enterprise", "indivial enterprise" and so on) to establish an enterprise name database
2. Select column a (the column of instrial enterprise name), enter the character "instrial enterprise" in the "name" column, and press enter to confirm
following the above operation, name columns B and C as "commercial enterprise" and "indivial enterprise"...
3. Switch to Sheet1, select the column (such as column C) that needs to input "enterprise category", execute the command "data → validity", and open the dialog box of "data validity". In the "Settings" tab, click the drop-down button on the right side of "allow", select the "sequence" option, and in the "source" box below, enter "instrial enterprise", "commercial enterprise", "indivial enterprise"... Sequence (each element is separated by a comma in English), and confirm to exit
select the column (e.g. column d) where you want to enter the enterprise name, and then open the data validity dialog box. After selecting the sequence option, enter the formula in the source box: = indirect (C1), and confirm to exit
4. Select any cell in column C (such as C4), click the drop-down button on the right, and select the corresponding "enterprise category" to fill in the cell. Then select the D column cell (such as D4) corresponding to the cell, and click the drop-down button to select the required enterprise name from the enterprise name list of the corresponding category and fill in the cell
prompt: if you do not need to print the "enterprise category" column in the future, you can select the column, right-click and select the "hide" option to hide the column< Second, create a new menu of "common documents"
create a new menu of "common documents" on the menu bar to add common workbooks to it, which is convenient to call at any time
1. Right click the mouse in the blank space of the toolbar and select the "Customize" option to open the "Customize" dialog box. In the command tab, select new menu under category, and then drag new menu under command to the menu bar
press the "change selection" button and enter a name (such as "common documents") in the "naming" box of the pop-up menu. 2. Select any item under "category" (such as "insert" option), and select any item under "command" on the right (such as "hyperlink" option), drag it to the new menu (common documents), and name it after the above operation (such as "payroll", etc.) to create the first document list name of the workbook
repeat the above operation to add more document list names
3. Select a menu item (such as payroll) in the common documents menu, right-click the mouse, and in the pop-up shortcut menu, select the "assign hyperlink → open" option to open the "assign hyperlink" dialog box. By pressing the drop-down button on the right side of "search range", locate the corresponding Workbook (such as "salary. XLS") folder, and select the workbook document
repeat the above operation to hyperlink the menu item with its corresponding workbook document
4. When you need to open a workbook document in the common documents menu in the future, just expand the common documents menu and click the corresponding option
tip: Although we drag the "hyperlink" option to the "common documents" menu, it does not affect the function of the "hyperlink" menu item in the "insert" menu and the "insert hyperlink" button on the "common" toolbar< Third, different types of data should be displayed in different colors.
in the payroll, if you want to display the total salary of 2000 yuan or more in red, 1500 yuan or more in blue, 1000 yuan or less in brown, and others in black, we can set it this way
1. Open the "payroll" workbook, select the column of "payroll", execute the "format → conditional format" command, and open the "conditional format" dialog box. Click the drop-down button on the right side of the second box, select the "greater than or equal to" option, and enter the value "2000" in the following box. Click format to open the cell Format dialog box, and set the color of font to red
2. Press the "add" button, and follow the above operation to set other conditions (greater than or equal to 1500, the font is set to "blue"; 2; Less than 1000, the font is set to "Brown")
3. After setting, press the "OK" button. Take a look at the payroll. Is the payroll data displayed in different colors according to your requirements< When editing professional forms, we often need to input some special professional symbols. In order to facilitate input, we can make our own "professional symbols" toolbar
1. Execute the command "tools → macro → record new macro", open the dialog box of "record new macro", and enter the macro name
7.
Converts a numeric value to text in the specified number format
grammar
text (value, format)_ Text)
a formula in which value is a numerical value and the calculated result is a numerical value, or a reference to a cell containing a numerical value
Format_ Text is a number format in the form of text in the category box on the number tab of the format cell dialog box
to call the cell command from the format menu, and then set the cell format on the number tab, you can only change the cell format without affecting the values. The function text
can be used to convert numeric values into formatted text, and the results will no longer be calculated as numbers
example:
where the text function gets the monetary text
8.
The steps of Excel text function to convert the value into text are as follows:
1. First, we select the cell to store the data
9. The validity of guardian card and guardian totem card is 30 days. Pledge one Guardian card / Guardian totem card, dig out 10 MPs / 100 MPs every day, and the monthly yield is 50% / 100%.
Hot content