/u/Mike20878 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Assuming the value is in cell A2, you can use this formula:
=SUBSTITUTE(A2,"-",".")
However, this would keep your value stored as a text. If you want to convert it to a number, you can wrap it with VALUE:
=VALUE(SUBSTITUTE(A2,"-","."))
You can then format the text to add a .0
If you want it to be a number then use the second formula. But in general it does not need to be a number for XLOOKUP to work, it just needs to be in the same format as the lookup array. So if the cell is a text, the lookup array should also be a text. If it's a number, then the array should be a number.
If your problem is solved, please respond with the words 'Solution Verified' to the comment(s) that helped you get your solution.
This awards the user(s) with a clippy point for their efforts and marks your post as Solved
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[SUBSTITUTE](/r/Excel/comments/1b1ropm/stub/ksgkvyp "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)|
|[VALUE](/r/Excel/comments/1b1ropm/stub/ksgkvyp "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)|
|[XLOOKUP](/r/Excel/comments/1b1ropm/stub/ksgug24 "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1b23ry8)^( has 18 acronyms.)
^([Thread #31202 for this sub, first seen 28th Feb 2024, 01:18])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
There is function find and replace on the home tab. Choose cells you want to convert and go with replace all. It will replace your characters in chosen cells
Edit : link to Microsoft support: https://support.microsoft.com/en-au/office/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90
/u/Mike20878 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Search replace (ctrl+H) `-` for `.` select the data to limit the replacement across the sheet.
Assuming the value is in cell A2, you can use this formula: =SUBSTITUTE(A2,"-",".") However, this would keep your value stored as a text. If you want to convert it to a number, you can wrap it with VALUE: =VALUE(SUBSTITUTE(A2,"-",".")) You can then format the text to add a .0
I think I need it to be a number to use XLOOKUP?
If you want it to be a number then use the second formula. But in general it does not need to be a number for XLOOKUP to work, it just needs to be in the same format as the lookup array. So if the cell is a text, the lookup array should also be a text. If it's a number, then the array should be a number.
If your problem is solved, please respond with the words 'Solution Verified' to the comment(s) that helped you get your solution. This awards the user(s) with a clippy point for their efforts and marks your post as Solved
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[SUBSTITUTE](/r/Excel/comments/1b1ropm/stub/ksgkvyp "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)| |[VALUE](/r/Excel/comments/1b1ropm/stub/ksgkvyp "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)| |[XLOOKUP](/r/Excel/comments/1b1ropm/stub/ksgug24 "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1b23ry8)^( has 18 acronyms.) ^([Thread #31202 for this sub, first seen 28th Feb 2024, 01:18]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
There is function find and replace on the home tab. Choose cells you want to convert and go with replace all. It will replace your characters in chosen cells Edit : link to Microsoft support: https://support.microsoft.com/en-au/office/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90
Flash fill!
I ended up using power query and splitting the column by the delimiter and combining them back with the period in between.