Excel replace value with same amount of characters
1
We are using Excel reports which are visible to all employees. The idea is to replace each digit in the cell's value with the digit "1". They can have an indication of the value, i.e. hundreds or thousands, but not the exact amount.
Here's an example:
ORD SUPPLY RATE
3430 329961.25 95.07
should be converted to
ORD SUPPLY RATE
1111 111111.11 11.11
Is it possible, and how? I've tried replace and counting the number of digits to replace, etc. but couldn't get to a definite answer.
microsoft-excel
edited Jan 10 at 6:58
Halflife
153
asked Nov 28 '16 at 9:19
BoetaBoeta
62
|
show 3 more comments
1
We are using Excel reports which are visible to all employees. The idea is to replace each digit in the cell's value with the digit "1". They can have an indication of the value, i.e. hundreds or thousands, but not the exact amount.
Here's an example:
ORD SUPPLY RATE
3430 329961.25 95.07
should be converted to
ORD SUPPLY RATE
1111 111111.11 11.11
Is it possible, and how? I've tried replace and counting the number of digits to replace, etc. but couldn't get to a definite answer.
microsoft-excel
edited Jan 10 at 6:58
Halflife
153
asked Nov 28 '16 at 9:19
BoetaBoeta
62
Do you need to do it with Find - Replace dialog, replacing numbers in the whole workbook / worksheet. Or you need a formula?
– Máté Juhász
Nov 28 '16 at 9:25
Whichever way can work, Its quite a large sheet (columns wise) so I suppose find - replace would be easiest, instead of having to apply formulas to each cell?
– Boeta
Nov 28 '16 at 9:27
Have a look here: codedawn.com/excel-add-ins.php; you can easily replace all numbers ([0-9]) to1
– Máté Juhász
Nov 28 '16 at 9:40
I have thought about doing something like =REPLACE(E2,1,LEN(E2),1) , which according to my understanding will repleace E2, starting at character 1 with X amount of 1's? But to no avail
– Boeta
Nov 28 '16 at 9:40
"But to no avail" - what exactly went wrong?
– Máté Juhász
Nov 28 '16 at 9:42
|
show 3 more comments
1
1
1
We are using Excel reports which are visible to all employees. The idea is to replace each digit in the cell's value with the digit "1". They can have an indication of the value, i.e. hundreds or thousands, but not the exact amount.
Here's an example:
ORD SUPPLY RATE
3430 329961.25 95.07
should be converted to
ORD SUPPLY RATE
1111 111111.11 11.11
Is it possible, and how? I've tried replace and counting the number of digits to replace, etc. but couldn't get to a definite answer.
microsoft-excel
edited Jan 10 at 6:58
Halflife
153
asked Nov 28 '16 at 9:19
BoetaBoeta
62
We are using Excel reports which are visible to all employees. The idea is to replace each digit in the cell's value with the digit "1". They can have an indication of the value, i.e. hundreds or thousands, but not the exact amount.
Here's an example:
ORD SUPPLY RATE
3430 329961.25 95.07
should be converted to
ORD SUPPLY RATE
1111 111111.11 11.11
Is it possible, and how? I've tried replace and counting the number of digits to replace, etc. but couldn't get to a definite answer.
microsoft-excel
microsoft-excel
edited Jan 10 at 6:58
Halflife
153
asked Nov 28 '16 at 9:19
BoetaBoeta
62
edited Jan 10 at 6:58
Halflife
153
asked Nov 28 '16 at 9:19
BoetaBoeta
62
edited Jan 10 at 6:58
Halflife
153
edited Jan 10 at 6:58
Halflife
153
edited Jan 10 at 6:58
Halflife
153
153
asked Nov 28 '16 at 9:19
BoetaBoeta
62
asked Nov 28 '16 at 9:19
BoetaBoeta
62
asked Nov 28 '16 at 9:19
BoetaBoeta
62
62
Do you need to do it with Find - Replace dialog, replacing numbers in the whole workbook / worksheet. Or you need a formula?
– Máté Juhász
Nov 28 '16 at 9:25
Whichever way can work, Its quite a large sheet (columns wise) so I suppose find - replace would be easiest, instead of having to apply formulas to each cell?
– Boeta
Nov 28 '16 at 9:27
Have a look here: codedawn.com/excel-add-ins.php; you can easily replace all numbers ([0-9]) to1
– Máté Juhász
Nov 28 '16 at 9:40
I have thought about doing something like =REPLACE(E2,1,LEN(E2),1) , which according to my understanding will repleace E2, starting at character 1 with X amount of 1's? But to no avail
– Boeta
Nov 28 '16 at 9:40
"But to no avail" - what exactly went wrong?
– Máté Juhász
Nov 28 '16 at 9:42
|
show 3 more comments
Do you need to do it with Find - Replace dialog, replacing numbers in the whole workbook / worksheet. Or you need a formula?
– Máté Juhász
Nov 28 '16 at 9:25
Whichever way can work, Its quite a large sheet (columns wise) so I suppose find - replace would be easiest, instead of having to apply formulas to each cell?
– Boeta
Nov 28 '16 at 9:27
Have a look here: codedawn.com/excel-add-ins.php; you can easily replace all numbers ([0-9]) to1
– Máté Juhász
Nov 28 '16 at 9:40
I have thought about doing something like =REPLACE(E2,1,LEN(E2),1) , which according to my understanding will repleace E2, starting at character 1 with X amount of 1's? But to no avail
– Boeta
Nov 28 '16 at 9:40
"But to no avail" - what exactly went wrong?
– Máté Juhász
Nov 28 '16 at 9:42
Do you need to do it with Find - Replace dialog, replacing numbers in the whole workbook / worksheet. Or you need a formula?
– Máté Juhász
Nov 28 '16 at 9:25
Do you need to do it with Find - Replace dialog, replacing numbers in the whole workbook / worksheet. Or you need a formula?
– Máté Juhász
Nov 28 '16 at 9:25
Whichever way can work, Its quite a large sheet (columns wise) so I suppose find - replace would be easiest, instead of having to apply formulas to each cell?
– Boeta
Nov 28 '16 at 9:27
Whichever way can work, Its quite a large sheet (columns wise) so I suppose find - replace would be easiest, instead of having to apply formulas to each cell?
– Boeta
Nov 28 '16 at 9:27
Have a look here: codedawn.com/excel-add-ins.php; you can easily replace all numbers (
[0-9]) to 1– Máté Juhász
Nov 28 '16 at 9:40
Have a look here: codedawn.com/excel-add-ins.php; you can easily replace all numbers (
[0-9]) to 1– Máté Juhász
Nov 28 '16 at 9:40
I have thought about doing something like =REPLACE(E2,1,LEN(E2),1) , which according to my understanding will repleace E2, starting at character 1 with X amount of 1's? But to no avail
– Boeta
Nov 28 '16 at 9:40
I have thought about doing something like =REPLACE(E2,1,LEN(E2),1) , which according to my understanding will repleace E2, starting at character 1 with X amount of 1's? But to no avail
– Boeta
Nov 28 '16 at 9:40
"But to no avail" - what exactly went wrong?
– Máté Juhász
Nov 28 '16 at 9:42
"But to no avail" - what exactly went wrong?
– Máté Juhász
Nov 28 '16 at 9:42
|
show 3 more comments
1 Answer
1
active
oldest
votes
1
Select the cells you wish to convert and run this short macro:
Option Explicit
Sub One_ification()
Dim r As Range, v As String
Dim L As Long, i As Long, CH As String
For Each r In Selection
v = r.Text
L = Len(v)
For i = 1 To L
CH = Mid(v, i, 1)
If CH Like "[0-9]" Then Mid(v, i, 1) = "1"
Next i
r.Value = v
Next r
End Sub
Before:
and after:
answered Nov 28 '16 at 12:27
Gary's StudentGary's Student
13.6k31730
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1150518%2fexcel-replace-value-with-same-amount-of-characters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Select the cells you wish to convert and run this short macro:
Option Explicit
Sub One_ification()
Dim r As Range, v As String
Dim L As Long, i As Long, CH As String
For Each r In Selection
v = r.Text
L = Len(v)
For i = 1 To L
CH = Mid(v, i, 1)
If CH Like "[0-9]" Then Mid(v, i, 1) = "1"
Next i
r.Value = v
Next r
End Sub
Before:
and after:
answered Nov 28 '16 at 12:27
Gary's StudentGary's Student
13.6k31730
add a comment |
1
Select the cells you wish to convert and run this short macro:
Option Explicit
Sub One_ification()
Dim r As Range, v As String
Dim L As Long, i As Long, CH As String
For Each r In Selection
v = r.Text
L = Len(v)
For i = 1 To L
CH = Mid(v, i, 1)
If CH Like "[0-9]" Then Mid(v, i, 1) = "1"
Next i
r.Value = v
Next r
End Sub
Before:
and after:
answered Nov 28 '16 at 12:27
Gary's StudentGary's Student
13.6k31730
add a comment |
1
1
1
Select the cells you wish to convert and run this short macro:
Option Explicit
Sub One_ification()
Dim r As Range, v As String
Dim L As Long, i As Long, CH As String
For Each r In Selection
v = r.Text
L = Len(v)
For i = 1 To L
CH = Mid(v, i, 1)
If CH Like "[0-9]" Then Mid(v, i, 1) = "1"
Next i
r.Value = v
Next r
End Sub
Before:
and after:
answered Nov 28 '16 at 12:27
Gary's StudentGary's Student
13.6k31730
Select the cells you wish to convert and run this short macro:
Option Explicit
Sub One_ification()
Dim r As Range, v As String
Dim L As Long, i As Long, CH As String
For Each r In Selection
v = r.Text
L = Len(v)
For i = 1 To L
CH = Mid(v, i, 1)
If CH Like "[0-9]" Then Mid(v, i, 1) = "1"
Next i
r.Value = v
Next r
End Sub
Before:
and after:
answered Nov 28 '16 at 12:27
Gary's StudentGary's Student
13.6k31730
answered Nov 28 '16 at 12:27
Gary's StudentGary's Student
13.6k31730
answered Nov 28 '16 at 12:27
Gary's StudentGary's Student
13.6k31730
answered Nov 28 '16 at 12:27
Gary's StudentGary's Student
13.6k31730
13.6k31730
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1150518%2fexcel-replace-value-with-same-amount-of-characters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Do you need to do it with Find - Replace dialog, replacing numbers in the whole workbook / worksheet. Or you need a formula?
– Máté Juhász
Nov 28 '16 at 9:25
Whichever way can work, Its quite a large sheet (columns wise) so I suppose find - replace would be easiest, instead of having to apply formulas to each cell?
– Boeta
Nov 28 '16 at 9:27
Have a look here: codedawn.com/excel-add-ins.php; you can easily replace all numbers (
[0-9]) to1– Máté Juhász
Nov 28 '16 at 9:40
I have thought about doing something like =REPLACE(E2,1,LEN(E2),1) , which according to my understanding will repleace E2, starting at character 1 with X amount of 1's? But to no avail
– Boeta
Nov 28 '16 at 9:40
"But to no avail" - what exactly went wrong?
– Máté Juhász
Nov 28 '16 at 9:42