# Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

Hello Nah,

Thanks for your feedback. As mentioned in the article, you can try any of the methods for your preferred hours. Only

the CONVERT functioncannot do that because it is built to perform on standard time format. Therefore, please check the attached Excel file where the working day is considered 8 hours/day.https://www.exceldemy.com/wp-content/uploads/2023/04/Convert-Hours-to-Days.xlsx

Thank you.

Regards,

Sanjida Mehrun Guria

Excel VBA & Content Developer

ExcelDemy

Hello Jon,

Thank you for sharing your query. I have gone through your shared dataset but the values and their required output seem quite unclear in your question. Still, I have tried to solve it. You will find the solution in the Excel file below.

https://www.exceldemy.com/wp-content/uploads/2023/04/Index-match-duplicate-values.xlsx

Please go through it and let us know if it is the solution that you are looking for. Otherwise, feel free to share your Excel file with [email protected] and we will look into this deeply.

Thank you.

Regards,

Sanjida Mehrun Guria

Excel VBA & Content Developer

ExcelDemy

Hello Jake,

Thanks for sharing your query. Please find the attached Excel file with the solution. Let us know for more queries.

https://www.exceldemy.com/wp-content/uploads/2023/04/Calculate-Distance-Between-Two-Cities.xlsx

Thank you.

Regards,

Sanjida Mehrun Guria

Excel VBA & Content Developer

ExcelDemy

Hello Luis,

Thank you for sharing your problem with us. As per your referred formula, I assume it is difficult to insert each product’s price in Column D for alignment. Because you need to have 2 separate datasets comprising Products and their Prices. Only after that, you can align them individually based on each category. For this, go through the following solutions that we described in this article.

Using VLOOKUP Function to Line up Two Sets of Data in Excelhttps://www.exceldemy.com/align-two-sets-of-data-in-excel/#1_Using_VLOOKUP_Function_to_Line_up_Two_Sets_of_Data_in_Excel

Use of Consolidate Feature for Summing Up Values within Two Sets of Data in Excelhttps://www.exceldemy.com/align-two-sets-of-data-in-excel/#5_Use_of_Consolidate_Feature_for_Summing_Up_Values_within_Two_Sets_of_Data_in_Excel

I hope it will help you. Let us know your feedback.

Thank you.

Regards,

Sanjida Mehrun Guria

Excel VBA & Content Developer

ExcelDemy

Hello Scott,

Thank you for sharing your problem. As per your query, you can simultaneously format specific text in

Bold,Italic,Underlineand even changeFont Sizewith the following code.`Sub Highlight_a_Single_Specific_Text_Case_Insensitive()`

`Text = InputBox("Enter the Specific Text: ")`

`Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))`

`For i = 1 To Selection.Rows.Count`

`For j = 1 To Selection.Columns.Count`

`For k = 1 To Len(Selection.Cells(i, j))`

`If LCase(Mid(Selection.Cells(i, j), k, Len(Text))) = LCase(Text) Then`

`Selection.Cells(i, j).Characters(k, Len(Text)).Font.ColorIndex = Color_Code`

`Selection.Cells(i, j).Characters(k, Len(Text)).Font.Bold = True`

`Selection.Cells(i, j).Characters(k, Len(Text)).Font.Italic = True`

`Selection.Cells(i, j).Characters(k, Len(Text)).Font.Underline = True`

`Selection.Cells(i, j).Characters(k, Len(Text)).Font.Size = 16`

`End If`

`Next k`

`Next j`

`Next i`

`End Sub`

Apply this code to the selected cells of your dataset and you will get the desired output.

I hope this solution will help you. Waiting to get your feedback.

Regards,

Guria

ExcelDemy

Hello Nicola,

Thank you for sharing your problem. I assume you are getting difficulties to calculate interest rate as changed twice in a year. In that case, calculate first 6 months’ rate separately with this formula

=7.5/12and later 6 months’ with=8/12. As a result, you will get the values of monthly interests.I hope this solution will help you. Let us know your feedback.

Regards,

Guria

ExcelDemy

Hello Donald Duck,

Thank you for sharing your wonderful tip to hide the secondary axis in Excel. I hope it will profoundly help our users.

Regards,

Guria

ExcelDemy

Hello Naureen,

Thank you for sharing your problem. I am replying on behalf of ExcelDemy. Assuming the student names are in

Column Aand the scores are inColumn B, insert this formula to get the top10scorers with duplicate values.`=INDEX(SORT(A2:B16,2,-1),SEQUENCE(10),1)`

If you want to get the names in another sheet, then just add the worksheet names in the formula (as shown below) and you will get your required output.

`=INDEX(SORT(‘Semester 2’!A2:B16,2,-1),SEQUENCE(10),1)`

I hope this solution will help you. Otherwise, feel free to share your workbook with

[email protected]and we will look into this deeply.Regards,

Guria

ExcelDemy

Hello ExcelIsAbandonware,

Thank you for sharing this Excel bug issue with the solution. I hope it will profoundly help our users.

Regards,

Guria

ExcelDemy

Hello Arif,

Thank you for sharing your query with us. As per your question, there can be 2 solutions to the problem.

1. If your number is

100000, then convert it toOne Lacwith this formula whereThousandis kept blank.`=CHOOSE(LEFT(TEXT(H6,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--LEFT(TEXT(H6,"000000000.00"))=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),2,1)=0,--MID(TEXT(H6,"000000000.00"),3,1)=0)," Lac"," Lac and ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ") &IF(--MID(TEXT(H6,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"), CHOOSE(MID(TEXT(H6,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) &IF((--LEFT(TEXT(H6,"000000000.00"))+MID(TEXT(H6,"000000000.00"),2,1)+MID(TEXT(H6,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(H6,"000000000.00"),4,1)+MID(TEXT(H6,"000000000.00"),5,1)+MID(TEXT(H6,"000000000.00"),6,1)+MID(TEXT(H6,"000000000.00"),7,1))=0,(--MID(TEXT(H6,"000000000.00"),8,1)+RIGHT(TEXT(H6,"000000000.00")))>0)," Crore and "," Crore ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--MID(TEXT(H6,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),5,1)=0,--MID(TEXT(H6,"000000000.00"),6,1)=0)," Lac"," Lac")) &CHOOSE(MID(TEXT(H6,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety") &IF(--MID(TEXT(H6,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(H6,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen")) &IF((--MID(TEXT(H6,"000000000.00"),4,1)+MID(TEXT(H6,"000000000.00"),5,1)+MID(TEXT(H6,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(H6,"000000000.00"),7,1)+MID(TEXT(H6,"000000000.00"),8,1)+MID(TEXT(H6,"000000000.00"),9,1))=0,--MID(TEXT(H6,"000000000.00"),7,1)<>0)," "," ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--MID(TEXT(H6,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),8,1)=0,--MID(TEXT(H6,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))& CHOOSE(MID(TEXT(H6,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ") &IF(--MID(TEXT(H6,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(H6,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))`

2. If your number is

123450, convert it into a word with this formula.`=CHOOSE(LEFT(TEXT(H6,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--LEFT(TEXT(H6,"000000000.00"))=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),2,1)=0,--MID(TEXT(H6,"000000000.00"),3,1)=0)," Lac"," Lac and ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ") &IF(--MID(TEXT(H6,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"), CHOOSE(MID(TEXT(H6,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) &IF((--LEFT(TEXT(H6,"000000000.00"))+MID(TEXT(H6,"000000000.00"),2,1)+MID(TEXT(H6,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(H6,"000000000.00"),4,1)+MID(TEXT(H6,"000000000.00"),5,1)+MID(TEXT(H6,"000000000.00"),6,1)+MID(TEXT(H6,"000000000.00"),7,1))=0,(--MID(TEXT(H6,"000000000.00"),8,1)+RIGHT(TEXT(H6,"000000000.00")))>0)," Crore and "," Crore ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--MID(TEXT(H6,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),5,1)=0,--MID(TEXT(H6,"000000000.00"),6,1)=0)," Lac"," Lac")) &CHOOSE(MID(TEXT(H6,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety") &IF(--MID(TEXT(H6,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(H6,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen")) &IF((--MID(TEXT(H6,"000000000.00"),4,1)+MID(TEXT(H6,"000000000.00"),5,1)+MID(TEXT(H6,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(H6,"000000000.00"),7,1)+MID(TEXT(H6,"000000000.00"),8,1)+MID(TEXT(H6,"000000000.00"),9,1))=0,--MID(TEXT(H6,"000000000.00"),7,1)<>0)," "," ")) &CHOOSE(MID(TEXT(H6,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--MID(TEXT(H6,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(H6,"000000000.00"),8,1)=0,--MID(TEXT(H6,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))& CHOOSE(MID(TEXT(H6,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ") &IF(--MID(TEXT(H6,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(H6,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(H6,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))`

I hope this solution will help you. Let us know your feedback.

Regards,

Guria

ExcelDemy

Hello Felix,

Thank you for sharing your query. Unfortunately, you cannot expressly create a hyperlink for a single word in a text string under a cell. By default, Excel creates a hyperlink for the whole cell. But you can change the cell appearance to show the hyperlink only to a single word. For this, do the following tasks.

• First, create a hyperlink for the whole text following any methods from this article.

• Then, select the words that you don’t want to be shown as hyperlinks.

• Now, remove

Underlinefrom the selected texts from theHometab or by pressingCtrl + Uon your keyboard.• Along with it, change the

Font ColortoAutomatic.• Finally, the output will look like this.

I hope this will help you with your problem. Eagerly waiting for your feedback.

Thank you.

Regards,

Guria

ExcelDemy

Hello Edmund Goh,

Thank you for sharing your problem with us. I assume you have properly gone through the steps to separate numbers with the

Text to Columnsfeature. The reason that any changes are not updating is because theText to Columnsfeature isstaticand it will not take any changes from the source cell. Yes, this is a limitation of this feature. You will also face the same if you apply theFlash Fillfeature. In this case, I will suggest you to apply any other methods from this article exceptMethod 4and5. Then you will be able to get changes on the separated numbers from the text.Let us know if it helps you.

Thank you.

Regards,

Guria

ExcelDemy.

Hello Theva,

Glad that you shared your query. As per your given example, if you want to simplify the first letter of each word along with numbers, apply this simple VBA code.

`Function ExtractFirstLetters(rng As Range) As String`

`Dim arry`

`Dim X As Long`

`arry = VBA.Split(rng, " ")`

`If IsArray(arry) Then`

`For X = LBound(arry) To UBound(arry)`

`ExtractFirstLetters = ExtractFirstLetters & Left(arry(X), 1)`

`Next X`

`Else`

`ExtractFirstLetters = Left(arry, 1)`

`End If`

`End Function`

Note that, if the words in your dataset are separated by

Hyphens(–) then insert this line in the code`arry = VBA.Split(rng, "-")`

Instead of this,

`arry = VBA.Split(rng, " ")`

I hope this will solve your problem. Let us know your feedback.

Regards,

Guria

ExcelDemy.

Hello Mike,

Thanks for sharing your problem. It seems fine for me when I tried to save the excel file shared in this article. The timestamp does not change in my case. Therefore, I will suggest you go through the steps carefully, no matter which method you prefer. Also, please check if the

Enable iterative calculationoption inFile>Options>Formulassection is turned on. If it still changes then try the VBA code shared in this article.https://www.exceldemy.com/timestamp-in-excel-when-cell-changes/#2_Apply_VBA_Code_to_Insert_Timestamp_in_Excel_When_Cell_Changes

Let us know if you can solve the issue. Otherwise, please send us the excel file that you are working on at this address [email protected].

Regards,

Guria

ExcelDemy.

Hello Violet,

Thanks for sharing your query. Yes, the

PMTdoes include tax or insurance. Therefore, you will provide $1308as thePMTin your spreadsheet.Let us know if you have more queries.

Regards,

Guria

ExcelDemy.

Hello Tony Woods,

Thank you for sharing your query. I have tried to change values in the

Levelcolumn and the timestamp changes according to the custom date format you shared. So it seems there is nothing wrong with your preferred custom format or the method that we described in the article. Therefore, I have come to the following solutions to your problem.Solution 1: Change theTextformat in theTimestampcolumnFor this, go to

Data>Textto Column.Then, in the second step, keep all the

Delimitersunchecked.Lastly, select

Dateas theColumn Data Format> choose your preferred format from the drop-down list > pressFinish.Solution 2: Check settings inFormulassectionFor this, go to

File>Optionsand then check if this setting is active in your workbook.Solution 3: Check settings inProofingsectionFor this, go to go to

File>Optionsand then check if the settings in theProofingsection are active in your workbook.I hope the solutions will help you to work efficiently. Let us know your feedback.

Regards,

Guria,

Exceldemy

Hello Allison,

Glad that you shared your problem. I have looked at it and therefore suggest these solutions.

If you want a drop-down list for some selected cells (

AG4:AG43) then replace`If Target.Address = "$AG$4" Then`

With

`If Target.Address = "$AG$4" Or Target.Address = "$AG$5" Or Target.Address = "$AG$6" …….. Or Target.Address = "$AG$43" Then`

If you want a drop-down list for the whole

AG Columnwhich is the33rd columnin the workbook then replace`If Target.Address = "$AG$4" Then`

With

`If Target.Column = 33 Then`

I hope the solutions will help you. Let us know your feedback.

Regards,

Guria,

Exceldemy

Hello Frank,

Thank you for sharing your query. I am replying to you on behalf of ExcelDemy. According to your question, I assume the dataset will look like this where the product “Ice Cream” is in two rows with different dates and prices.

Now, to solve your problem, you have to put a date in Criteria 2 that lies in between the dates you want a price from. Afterward, apply the same formula as we used earlier and it will show the accurate price.

`=INDEX($E$5:$E$16,MATCH(1,(($B$5:$B$16=G5)*($D$5:$D$16>=H5)*($C$5:$C$16<=H5)),0))`

I hope this solution will help you. Let us know your feedback.

Thanks!

Hello Tom,

I am replying to you on behalf of ExcelDemy. It is very unfortunate to know that, you are facing such difficulties. I have gone through your queries and thought of giving the following solutions.

1. First of all, please check this link in case you unintentionally protected your excel file through any of the methods described.

https://www.exceldemy.com/protect-excel-sheet-from-copy-paste/

2. When any worksheet or workbook is password protected, you cannot see them in actual letters, rather than some dots. Therefore, if you cannot unprotect the sheet with the help of this article, I am referring you to another one with some easy VBA codes to unprotect your file permanently without knowing the password.

https://www.exceldemy.com/unprotect-all-sheets-in-excel-vba/

3. You also mentioned that you are having trouble printing the worksheet. In this case, check this solution to solve this issue.

https://www.exceldemy.com/excel-margins-not-printing-correctly/

I hope the solutions will help you. Let us know your feedback.

Thank you!

Hello Mirela,

Thank you for sharing your query. It would be really great if you can send the workbook to [email protected] as It seems quite confusing to create a dataset with the information you shared. After that, I will try to solve your problem. Hope to hear from you soon.

Thanks!

Hello Russell,

Thank you for sharing your query. I hope the following solution will work for you.

First, click on the

Starticon and selectSettings.Then, select

Appsin the new window.Next, go to the

Default appssection on the left panel.Here, check if the application for

Emailis selected asMail.If not, then click on it and choose

Mailfrom the list of applications.After this, I recommended you restart your device once. I hope it will solve your problem. Please let me know your feedback.

Thanks!

Hi Jacek,

Thank you for sharing your query. To solve this, you can try another formula instead after copying the Code 128 Font in the

C:\Windows\Fontsfolder.First, insert this formula for the data

456987321596328and press Enter.`="*"&B5&"*"`

Then, change the Font to

Code 128and the Size to36for creating the barcode.Make sure, your input data is in Text format before applying the formula.

Moreover, VBA is not error-free. So I suggest you create a new Excel-Macro Enabled Workbook and copy-paste the VBA code to create your own barcode.

I hope this will help you to solve your problem. Please let us know if you have other queries.

Thanks!

Hi Muniru Tahiru,

Thank you for sharing your problem. To solve this you can go through this article. I hope it will help you.

https://www.exceldemy.com/automatically-update-one-excel-sheet-from-another-sheet/#top_ankor

Please let us know if you have other queries.

Thanks!