Calculation on Strings

K

kwebihaf

New Member
Joined
Sep 18, 2013
Messages
4
Suppose I have a string like TBU/EVE/234 and i want the next strings to be TBU/EVE/235, TBU/EVE/236,TBU/EVE/237 in different cells.

what formula or functions can i use to achieve that

Thanks in advance for your quick help.

FRED
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
kwebihaf,

Welcome to the MrExcel forum.

We can not tell what cells, rows, columns your raw data strings are in.

And, we can not what cells, rows, columns your results are in.

What cell is TBU/EVE/234 in?

And, what cells are the following in?
TBU/EVE/235
TBU/EVE/236
TBU/EVE/237
 
Upvote 0
kwebihaf,

Sample raw data:


Excel 2007
ABCD
1TBU/EVE/234
2TBU/EVE/111
3TBU/EVE/222
4EVE/TBU/1000
5
Sheet1


After the macro:


Excel 2007
ABCD
1TBU/EVE/234TBU/EVE/235TBU/EVE/236TBU/EVE/237
2TBU/EVE/111TBU/EVE/112TBU/EVE/113TBU/EVE/114
3TBU/EVE/222TBU/EVE/223TBU/EVE/224TBU/EVE/225
4EVE/TBU/1000EVE/TBU/1001EVE/TBU/1002EVE/TBU/1003
5
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub CalculateStrings()
' hiker95, 09/18/2013
' http://www.mrexcel.com/forum/excel-questions/727229-calculation-strings.html#post3578407
Dim c As Range, s, d As Long
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  s = Split(c, "/")
  For d = 1 To 3 Step 1
    c.Offset(, d) = s(0) & "/" & s(1) & "/" & s(2) + d
  Next d
Next c
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CalculateStrings macro.
 
Upvote 0
hiker95 said:
kwebihaf,

Sample raw data:

Excel 2007
ABCD
1TBU/EVE/234
2TBU/EVE/111
3TBU/EVE/222
4EVE/TBU/1000
5

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​



After the macro:

Excel 2007
ABCD
1TBU/EVE/234TBU/EVE/235TBU/EVE/236TBU/EVE/237
2TBU/EVE/111TBU/EVE/112TBU/EVE/113TBU/EVE/114
3TBU/EVE/222TBU/EVE/223TBU/EVE/224TBU/EVE/225
4EVE/TBU/1000EVE/TBU/1001EVE/TBU/1002EVE/TBU/1003
5

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub CalculateStrings()
' hiker95, 09/18/2013
' http://www.mrexcel.com/forum/excel-questions/727229-calculation-strings.html#post3578407
Dim c As Range, s, d As Long
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  s = Split(c, "/")
  For d = 1 To 3 Step 1
    c.Offset(, d) = s(0) & "/" & s(1) & "/" & s(2) + d
  Next d
Next c
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CalculateStrings macro.
Click to expand...

Thanks for the Macro Code, hiker95.

Exactly that is how I wanted the results to be.

But is there any Excel function/formula that i can use rather than the Macro code?

Regards,

FRED
 
Upvote 0
kwebihaf,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


You are very welcome. Glad I could help.

Thanks for the feedback.


But is there any Excel function/formula that i can use rather than the Macro code?
Click to expand...

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
kwebihaf said:
Exactly that is how I wanted the results to be.

But is there any Excel function/formula that i can use rather than the Macro code?
Click to expand...
Assuming your data starts in cell A2, this formula placed in B1 and copied down to the last row with data, and then all those cells copied across to the last column you want to fill, seems to work...

=LEFT(A1,FIND("/",A1,FIND("/",A1)+1)-1)&"/"&MID(A1,FIND("/",A1&"//",FIND("/",A1&"/")+1)+1,99)+1
 
Upvote 0
Thanks Rick.

That formula helps and has worked.

Another thing, what if I want the results down the columns, say from Cell B1 to Cell B100.

How do I go about it?

Regards,

FRED
 
Upvote 0
kwebihaf said:
Thanks Rick.

That formula helps and has worked.

Another thing, what if I want the results down the columns, say from Cell B1 to Cell B100.

How do I go about it?

Regards,

FRED
Click to expand...
Hi Fred

Perhaps;


Excel 2010
ABCD
1TBU/EVE/234TBU/EVE/111TBU/EVE/222EVE/TBU/1000
2TBU/EVE/235TBU/EVE/112TBU/EVE/223EVE/TBU/1001
3TBU/EVE/236TBU/EVE/113TBU/EVE/224EVE/TBU/1002
4TBU/EVE/237TBU/EVE/114TBU/EVE/225EVE/TBU/1003
5TBU/EVE/238TBU/EVE/115TBU/EVE/226EVE/TBU/1004
Sheet2
Cell Formulas
RangeFormula
A2=SUBSTITUTE(A1,LOOKUP(10^308,--RIGHT(A1,{1;2;3;4;5;6})),"")&LOOKUP(10^308,--RIGHT(A1,{1;2;3;4;5;6}))+1
 
Upvote 0
Worksheet Formulas
CellFormula
A2=SUBSTITUTE(A1,LOOKUP(10^308,--RIGHT(A1,{1;2;3;4;5;6})),"")&LOOKUP(10^308,--RIGHT(A1,{1;2;3;4;5;6}))+1

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Click to expand...

Thanks.

It Works also on Excel 2007.

I am grateful for the quick help.

Regards,

FRED
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Master worksheet automatically updating other sorted worksheets.
Replies
8
Views
488
Fluff
Fluff
C
  • Question
Nested VLOOKUP I think
Replies
4
Views
497
CordingBags
C
O
  • Question
Change MS Word Doc Properties via MS Excel VBA Macro
Replies
3
Views
177
NdNoviceHlp
NdNoviceHlp
A
  • Solved
Countifs multiple criteria across a row
2 3
Replies
20
Views
244
Fluff
Fluff
A
  • Solved
Auto Serial Number in a Cell
2 3
Replies
23
Views
484
arijitirf
A

Forum statistics

Threads
1,216,723
Messages
6,132,323
Members
449,718
Latest member
Marie42719

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