how i can make it?

SBAY3I

New Member
Joined
Jul 21, 2018
Messages
45
hello all members
i got some problems in excel. one of them is i want to make

72653/m/17 like that ***53/m/17
101371/f/17 like that ****71/f/17
72673/m/17 like that ***73/m/17
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Another way, in case the length of characters are variable after the slash "/"

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">72653/m/17</td><td style=";">***53/m/17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">101371/f/17</td><td style=";">****71/f/17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">72673/m/17</td><td style=";">***73/m/17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">72653/mf/17</td><td style=";">***53/mf/17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">101371/f/171</td><td style=";">****71/f/171</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">72673/mf/171</td><td style=";">***73/mf/171</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet137</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=REPT(<font color="Blue">"*",LEN(<font color="Red">LEFT(<font color="Green">A1,FIND(<font color="Purple">"/",A1</font>)-3</font>)</font>)</font>)&MID(<font color="Blue">A1,FIND(<font color="Red">"/",A1</font>)-2,255</font>)</td></tr></tbody></table></td></tr></table><br />
 

SBAY3I

New Member
Joined
Jul 21, 2018
Messages
45
thank you very much friends for your help
by the way the the colomn was in three colomn and i make them like that 72653/m/17
are your formulas can help me in this casse
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
AB
172653/m/17***53/m/17
2101371/f/17****71/f/17
372673/m/17***73/m/17
472653/mf/17***53/mf/17
5101371/f/171****71/f/171
672673/mf/171***73/mf/171

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

Please explain clearly,

Are you saying 72653/m/17 is in three separate columns Without the slash "/"?
So 72653 in A1, m in B1, 17 in C1?
And, you want the result to be ***53/m/17 in One Cell With the slash "/"?
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

assume A1 for number then ="***" & RIGHT(A1,2)
 

SBAY3I

New Member
Joined
Jul 21, 2018
Messages
45
NO I MEAN 72653 WAS IN A1, AND M WAS IN B1, AND 17 WAS IN C1, AND I MAKE THEM IN ONE CELL LIKE 72653/M/17 WITH THIS FORMULA =A1&"/"&B1&"/"&C1
WHAT I WANT IS ***53/m/17 in One Cell With the slash "/" BUT I THINK THE FORMULA WHICH I PUT BEFOR BLOCKING YOURS =REPT("*",LEN(LEFT(A1,FIND("/",A1)-3)))&MID(A1,FIND("/",A1)-2,255)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Then do this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">72653</td><td style=";">m</td><td style="text-align: right;;">17</td><td style=";">***53/m/17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">101371</td><td style=";">f</td><td style="text-align: right;;">17</td><td style=";">****71/f/17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">72673</td><td style=";">m</td><td style="text-align: right;;">17</td><td style=";">***73/m/17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">72653</td><td style=";">mf</td><td style="text-align: right;;">17</td><td style=";">***53/mf/17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">101371</td><td style=";">f</td><td style="text-align: right;;">171</td><td style=";">****71/f/171</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">72673</td><td style=";">mf</td><td style="text-align: right;;">171</td><td style=";">***73/mf/171</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet137</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=REPT(<font color="Blue">"*",LEN(<font color="Red">A1</font>)-2</font>)&RIGHT(<font color="Blue">A1,2</font>)&"/"&B1&"/"&C1</td></tr></tbody></table></td></tr></table><br />

D1 formula copied down.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,147
Members
409,804
Latest member
aceyus_michael

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top