Hey everyone, I've looked at a lot of posts that come close, but I believe the steps I need are compound.
In cell A1 I have the following text, numbers and a line break:
Instruction: 100
Review: 34
I would like to mirror this data in B1, while adding a number(we'll use 10 for example) to each number in A1, resulting in:
Instruction: 110
Review: 44
It's ok for the line break to be lost but I'd prefer to keep it in the results.
Any ideas?
Thanks,
Matt
Is there a condition as to the numbers that will be present?
For example, the numbers will not be greater than 100 or less that 0?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
The numbers will never be less than 0 or greater than 1000.
Additionally, the final values can omit the text and past just the numbers, if easier.
Example result for B1:
110
44
Thanks!
Try this...
Data Range
Review: 34
Review: 44
This formula entered in B1:
="Instruction: "&LOOKUP(1001,--MID(A1,13,{1,2,3,4,5}))+10&CHAR(10)
&"Review: "&LOOKUP(1001,--RIGHT(A1,{1,2,3,4,5}))+10
Format to wrap text.
Last edited by Tony Valko; 03-19-2014 at 03:49 PM. Reason: replaced ; with ,
That works! However, the text varies at times from Instruction, to Assessment, to Review, etc.
Is there a way to omit the text from the results?
Thanks,
Matt
Mine is much more complex than Tony's. See the Defined names to get "Firstdigits" and SecondDigits"
Then
=REPLACE(REPLACE(A1, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")), firstdigits, MID(A1, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789")), firstdigits)+10),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789",FIND(CHAR(10),A1))), SecondDigits, MID(A1, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789",FIND(CHAR(10),A1))),SecondDigits)+10)
See attachment
Sure it's worth it?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
For just the numbers:
=LOOKUP(1001,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))+10&CHAR(10)&LOOKUP(1001,--RIGHT(A1,{1,2,3,4,5}))+10
Tony,
Your last post is working perfectly with 1 exception. I'm not getting a line break inserted between my results. Is there a way we can do that? Wrap text is selected. If not, you've still greatly helped me!
ChemistB,
When I open your excel file I get #VALUE! as a result. Keep in mind, I'm on a mac using Excel 2011 V 14.3.8 I imagine this might be the problem.
Thanks!,
Matt
Works OK for me.
The formula will return 11044 and after you format to wrap text you should get the result as displayed below:
Data Range
Review: 34
44
You might have to adjust the column width to get the correct display.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks