Challenge :) formula or VBA

D

DavidG007

Board Regular
Joined
Jul 6, 2018
Messages
79
Office Version
  1. 365
Platform
  1. Windows
I have an issue that I don't know how to easily resolve. I need to convert the following line of text.

Column 'C1' would typically be this (but will be multiple lengths);
Case Is = "400000","400100","400200","400300","400400","400500","400700","400900"

Column 'D2' would say 'Revenue'

What I need to be able to do is break down Column 'C1' and include the text in Column 'D2'

My final answer would look like this;

Column A Column B
400000 Revenue
400100 Revenue
400200 Revenue


etc etc.

The formula or code would then need to do exactly the same check on subsequent rows as the pattern

I really hope this makes sense and there are a few of you that fancy the challenge :)

massive thanks for any replies
 
Sort by date Sort by votes
are you interested only in numbers like "435000","436000","436010","437000" and the word "Revenue" ,
or all words after word Return ?
 
Last edited:
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]BSdefaultValue[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2[/td][td]BSdefaultValue[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]3[/td][td=bgcolor:#E2EFDA]BSdefaultValue[/td][/tr]

[tr=bgcolor:#FFFFFF][td](Bypass)[/td][td]"None"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]400000[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]400100[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]400200[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]400300[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]400400[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]400500[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]400700[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]400900[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]400901[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]440000[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]470000[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]491000[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]492000[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]495000[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]431000[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]431005[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]432000[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]432010[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]433000[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]433010[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]434000[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]434010[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]435000[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]436000[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]436010[/td][td=bgcolor:#E2EFDA]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]437000[/td][td]"Revenue"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]500203[/td][td=bgcolor:#E2EFDA]"COS_CFR"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]500201[/td][td]"COS_LFR"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]500202[/td][td=bgcolor:#E2EFDA]"COS_MFR"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]500204[/td][td]"COS_SaaS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]500000[/td][td=bgcolor:#E2EFDA]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]500200[/td][td]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]500900[/td][td=bgcolor:#E2EFDA]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]501000[/td][td]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]520000[/td][td=bgcolor:#E2EFDA]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]521000[/td][td]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]531000[/td][td=bgcolor:#E2EFDA]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]532000[/td][td]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]533000[/td][td=bgcolor:#E2EFDA]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]533010[/td][td]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]534000[/td][td=bgcolor:#E2EFDA]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]534010[/td][td]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]535000[/td][td=bgcolor:#E2EFDA]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]536000[/td][td]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]537000[/td][td=bgcolor:#E2EFDA]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]539510[/td][td]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]658600[/td][td=bgcolor:#E2EFDA]"COS"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]699999[/td][td]"ADM"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]810100[/td][td=bgcolor:#E2EFDA]"ADM"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]830500[/td][td]"ADM"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]850000[/td][td=bgcolor:#E2EFDA]"ADM"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]850500[/td][td]"ADM"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]851000[/td][td=bgcolor:#E2EFDA]"ADM"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]731000[/td][td]"Fin_Expense"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]810000[/td][td=bgcolor:#E2EFDA]"Fin_Expense"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]810010[/td][td]"Fin_Expense"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]810015[/td][td=bgcolor:#E2EFDA]"Fin_Expense"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]810050_1[/td][td]"Fin_Expense"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]810070_1[/td][td=bgcolor:#E2EFDA]"Fin_Expense"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]810071_1[/td][td]"Fin_Expense"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]820100[/td][td=bgcolor:#E2EFDA]"Fin_Expense"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]820200[/td][td]"Fin_Expense"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]880830[/td][td=bgcolor:#E2EFDA]"Fin_Expense"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]800000[/td][td]"Fin_Income"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]810050[/td][td=bgcolor:#E2EFDA]"Fin_Income"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]860000[/td][td]"Fin_Income"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]869080[/td][td=bgcolor:#E2EFDA]"Fin_Income"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]880840[/td][td]"Fin_Income"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]830300[/td][td=bgcolor:#E2EFDA]"SR_Associates"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]869090[/td][td]"Taxation"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]880000[/td][td=bgcolor:#E2EFDA]"Taxation"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]880100[/td][td]"Taxation"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]880200[/td][td=bgcolor:#E2EFDA]"Taxation"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]880300[/td][td]"Taxation"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]880400[/td][td=bgcolor:#E2EFDA]"Taxation"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]880500[/td][td]"Taxation"[/td][/tr]
[/table]
 
Upvote 0
sandy666 said:
are you interested only in numbers like "435000","436000","436010","437000" and the word "Revenue" ,
or all words after word Return ?
Click to expand...



Hi Sandy,

It would need to show all the words after Return, so 500201 would return COS_LFR

Thanks
 
Upvote 0
so here is:

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Fill = Table.FillUp(Source,{"Column2"}),
    Filter = Table.SelectRows(Fill, each ([Column1] <> null)),
    RemoveBR = Table.RemoveLastN(Filter,1),
    Replace1 = Table.ReplaceValue(RemoveBR,"Case Is = ","",Replacer.ReplaceText,{"Column1"}),
    Extract = Table.TransformColumns(Replace1, {{"Column1", each Text.BeforeDelimiter(_, " '"), type text}}),
    Replace2 = Table.ReplaceValue(Extract,"Return ","",Replacer.ReplaceText,{"Column2"}),
    Split = Table.ExpandListColumn(Table.TransformColumns(Replace2, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    Trim = Table.TransformColumns(Split,{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}})
in
    Trim[/SIZE]

example file
 
Upvote 0
Hi Sandy, unfortunately it won't let me open your sample file, i get the following error?

[FONT=&quot]We're sorry. We can't open the workbook in the browser because it uses these unsupported features: [/FONT]

[FONT=&quot]• XML maps[/FONT]
 
Upvote 0
you need to DOWNLOAD file not open. Excel Online doesn't support PowerQuery

you've option there: open in Excel - use it
 
Last edited:
Upvote 0
Hi Sandy, really sorry but I don't see an option to download or open in excel? if i right click on you link the option doesn't appear.

Sorry for appearing dim, thank you for your help
 
Upvote 0
in FireFox (the best browser! :laugh: )

open.jpg


or you can try this: excel file
 
Last edited:
Upvote 0
Hi, this is proving more of a challenge than the original query!, i have just opened firefox to try your steps above but i still don't see the option to 'open in excel'??
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
SUM and CHOOSE formula question
Replies
7
Views
260
Fluff
Fluff
U
  • Solved
REPT function excel
Replies
6
Views
109
RoryA
RoryA
T
  • Solved
Challenge Question: A Quest for Elegance
Replies
6
Views
556
Peter_SSs
Peter_SSs
S
  • Question Question
VBA Challenge
Replies
2
Views
497
SnowyBry
S
K
  • Question Question
Revenue ramp up and ramp down formula needed
Replies
0
Views
2K
KSU1016
K

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top